CODEWITHSUNDEEP
excelvba
Shivid
Shivid

Reputation: 1343

List Box Form Control selectrion triggers Worksheet SelectionChange

I am trying to keep track of changes (to be appeared in a chart) upon every changes made in the worksheet using this reference (codes are copied below as example reference).

The changes in the worksheet actually can be made by several List Boxes (form Control Menu) and other cells e.g. Input 3 and 4. Let's say the resulting value caused by any change appears in the cell G14, and as the example code I want to keep them in I14 (as in the example image). enter image description here The formula might be e.g. D14 + E14 + K14 + L14 where K14 and L14 are values linked to the ListBoxes. Selections in Input 3 and 4 trigger Worksheet_Change but selections in List Box apparently don't, shall I add a macro for List Box callback in which Worksheet_Change gets triggered?

Apart from that, if there is any better saving the results method, please mention it.
Many thanks in advance!

Dim xVal As String

Private Sub Worksheet_Change(ByVal Target As Range)
    Static xCount As Integer

    Application.EnableEvents = False
    If Target.Address = Range("G14").Address Then
        Range("I14").Offset(xCount, 0).Value = xVal
        xCount = xCount + 1
    Else
        If xVal <> Range("G14").Value Then
         Range("I14").Offset(xCount, 0).Value = xVal
        xCount = xCount + 1
        End If
    End If
    Application.EnableEvents = True
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    xVal = Range("G14").Value
End Sub

Upvotes: 1

Views: 86

Answers (2)

Shivid
Shivid

Reputation: 1343

Tahnks to @Naresh, solved the problem in the following way, any improvement editis more than welcome since I know the codes might seem inefficient!

Dim xVal As String

Public Function customRecorder(Target As String)
    Static xCount As Integer
    Application.EnableEvents = False
    Range("I14").Offset(xCount, 0).Value = xVal
    xCount = xCount + 1
    Application.EnableEvents = True
End Function

Private Sub Worksheet_Calculate()
    xVal = Range("G14").Value
    customRecorder (Range("G14"))
End Sub

Upvotes: 0

Naresh
Naresh

Reputation: 3034

As you are asking about trigger event to run a macro on selection of listbox value (form control), I believe this could be considered as answer. I am sure someone with better knowledge will improve it.

Worksheet_Change event occurs when a cell or range of cells is changed manually (not by any auto process like calculation or selection of listbox value)

There can be different scenarios

First

There is no link between calculation of cells affected by the listbox change. Say, in First ListBox, you select 10, and cell A2 is updated to 10 and cell A3 is calculated value changed to 30. Then You select in second listbox 15 and cell B2 is changed to 15 and cell B3 is calculated independently if A2 and A3 to 90.In this case these are independent listboxes and their results. So, in this case, you can assign macro to each listbox which will run every time you change value in each listbox.

Second

Dependent calculations: Say the desired result for tracking is in D3 which will be calculated only on selection of four listbox values in A2, B2, C2 and D2. In this case you will not like to run macro for every list change but only after selecting values in all listboxes and calculation of D3. So in that case, instead of assigning macros to all the listboxes you could assign it only to last listbox.

Third

By now you must have understood there are two events taking place. One is selection of listbox value and second is calculation. In the second scenario, if you want to run macro for every change in calculation, say when you select A2 and when you select B2, etc. then you can simply use calculation event instead of assigning macro to every listbox. It will run everytime when a value is changed causing worksheet to calculate.

For assigning macro to listbox (form control) --- You can directly assign a macro to the list box.. First create a macro in VBA . Then Just right click on the list box and click assign macro. then select a macro to be assigned. .. The macro will be run when you click the listbox to change the value

Also, as you want to track the result calculated with macro, you need the sheet to be calculated first. Start the macro with Worksheet.Calculate method to be safe (in case formula results are not updated for some reason).

Upvotes: 1

Related Questions