calling a macro based on a cell input

[cthompson]

I am trying to call a simple macro based on a cell input.

In this case i want macro3 to run anytime a number between 1 and 12 is entered at cell "Q1". in this case i am selecting the month number at "q1" and instead of assigning the macro to a button i just want it to run when the number is entered.

Any suggestions, and thanks again always for the help.

Chris



Below is a partial copy of the macro

Sub Macro3()
'
' Macro3 Macro

Range("Q1").Select

If Range("q1") = 1 Then
Columns("AF:AQ").Select
Selection.EntireColumn.Hidden = False
Columns("Ag:Aq").Select
Selection.EntireColumn.Hidden = True
Range("q10").Select
End If
End Sub

 

[Joe4]

You can use a Worksheet_Change event procedure, which is VBA code that is automatically triggered when a cell is manually updated. It needs to be placed in the proper sheet module, and must be named a certain way.
The easiest way to get there is to go to the sheet you want to apply it, right-click on the sheet tab name found at the bottom of the screen, select "View Code", and paste this code in the resulting VB Editor window:

Private Sub Worksheet_Change(ByVal Target As Range)
    
    If Intersect(Target, Range("Q1")) Is Nothing Then Exit Sub
    
    If (Range("Q1") >= 1) And (Range("Q1") <= 12) Then
        Call Macro3
    End If

End Sub

If a value between 1 and 12 is entered in cell Q1, it will call your Macro3 to run.

 

[Fluff]

You can use a change event for that

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Target.Address(0, 0) = "Q1" Then
        Select Case Target.Value
            Case 1 To 12
                Columns("AF:AQ").Hidden = False
            Case Else
                Columns("Ag:Aq").Hidden = True
        End Select
    End If
    Range("q10").Select
End Sub

Put this in the sheet module, for the sheet you want it to work on.

 

[cthompson]

This is so cool!!! Yes i admit that i am a nerd, but thank you both for the help it makes things work so much more smoothly.
Chris

 

[Joe4]

You are weclome. Glad we were able to help.

Yes i admit that i am a nerd

Then you have come to the right place! You are amongst your people now!