Problem with Worksheet_Calculate() / Worksheet_Change()

[Brett Thompson]

Hello all
Hoping for some help on a simple workbook we would like to put together...
Using a combo box from form control; we would like the 'cell link' value, derived from the value selected in the combo box, to automatically trigger a macro. As a concept, the workbookcurrently has 2 macros, but there would be more when finished.
When I point the VBA at a manually changing cell, the macros run & hide / unhide the columns as excepted, but we can't get it to work from the result of the combo box.

Sub ReEnableEvents()
    Application.EnableEvents = True
End Sub


Sub Worksheet_Calculate()
    Dim target As Range
    Set target = Range("D2")
    If Not Intersect(target, Range("D2")) Is Nothing Then
    End If
    If target.Value = 1 Then
    Call ShowAll
    End If
    If target.Value = 2 Then
    Call ShowHandS
    End If
End Sub

Hope this makes sense? Thanks for reading & any questions, please ask...

Many thanks
B

 

[RoryA]

Why not assign a macro to the form control directly?

 

[Brett Thompson]

Thanks Rory.

I'd put that down to a severe lack of knowledge on my part. How would I do that?

Cheers
B

 

[Brett Thompson]

Sorry, Rory...Just to add. I'm not sure how you assign conditional macros to form control. I can see how a single macro can be assigned...

B

 

[RoryA]

You'd create a macro that has the conditions in it - for example:

Sub CheckValue()
If Range("D2").Value = 1 Then
Call ShowAll
ElseIf Range("D2").Value = 2 Then
Call ShowHandS
    End If
End Sub

 

[Brett Thompson]

Thank youj, Rory - very much appreciated.

 

[RoryA]

Glad to help.