VBA Worksheet_Change with Conditional If Statements

[ljrezn]

Hi, I have created three macros that will format the worksheet three different ways, for printing purposes. (changes on amount of information filled in tables). Anyways, currently I have those three macros assigned to three different icons. I would like to change this so that the appropriate macro is run based off a user input cell when that cell is changed.

To rephrase, I have a validation list with three options, (A,B,C) with three corresponding macros (1,2,3). When the input cell is A, I want Macro1 to run. Input=B, then Macro2. Input=C, then Macro3. This is what I have so far, but can not get it to work.
​

Private Sub Worksheet_Change(ByVal Target As Range)
 
        If Target.Address = InputCell Then
            If InputCell = A Then
                Call Macro1
            End If
             If InputCell = B Then
                Call Macro2 
           End If
             If InputCell = C Then
                Call Macro 3
            End If
        End If
End Sub

If anyone has any other ideas of Code structure that would be greatly appreciated. I am new at writing my own codes in VBA and are not sure about all my options.

Thanks,

 

[njimack]

Something like this:

Private Sub Worksheet_Change (ByVal Target As Range)
Application.EnableEvents = False

Select Case Target.Value
Case A: Call Macro1
Case B: Call Macro2
Case C: Call  Macro3
End Select

Application.EnableEvents = True
End Sub

 

[ljrezn]

Thanks for you help, but unfortunately it is still not working. When I change the cell value it is not even running I believe.

I do have the code posted on under the proper Sheet Object. The three macros referenced are also under that sheet. Should they be in the module?

 

[AlphaFrog]

The three macros should be in a separate module.

Run this macro once to make sure the Event triggers are enabled

Sub Enable_Events()
    Application.EnableEvents = True
End Sub

Put this in the worksheet module and change $A$1 to the address of the cell that has the drop-down list.

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    On Error GoTo Reenable
    If Target.Address = "[COLOR="Red"]$A$1[/COLOR]" Then
        Select Case Target.Value
            Case A: Call Macro1
            Case B: Call Macro2
            Case C: Call Macro3
        End Select
    End If
Reenable:
    Application.EnableEvents = True
End Sub

 

[ljrezn]

Thank you! That works great. I was missing the Quotes around each of the Case values. (Case "A")

The macro is properly functioning and resizing, but it seems that the macro does not behave exactly the same as if I ran the individual macro. Any ideas as why this may be occuring? Here is a sample of one of the macros referenced.

Sub Resize_1_OP()
'
' Resize_1_OP Macro
'
'
    Worksheets(3).Unprotect Password:="***"
    Worksheets(3).Rows("39:60").EntireRow.AutoFit
    Worksheets(3).Rows("78:99").EntireRow.AutoFit
    Worksheets(3).Rows("39:60").RowHeight = 0
    Worksheets(3).Rows("78:99").RowHeight = 0
    Worksheets(3).Range("A62").Select
    Worksheets(3).HPageBreaks(1).Delete
    Worksheets(3).PageSetup.PrintArea = "$A$12:$Q$99"
    
    Worksheets(3).Range("B27:C38,D27:O38,B66:C77,D66:O77").Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    
    Worksheets(3).Protect Password:="***"
    
End Sub

The Worksheet change macro is being triggered by a user defined value on sheet 2 to format sheet 3 as shown above.

 

[ljrezn]

I just noticed that if I run the macro from another active sheet I receive the following error. Select method of Range class failed.

 

[AlphaFrog]

This line will error if Worksheet(3) is not the active sheet.

<font face=Courier New>Worksheets(3).Range("A62").Select</FONT>

You would have to select the worksheet before you can select a cell on that worksheet. I don't think you need to select cell A62 for your macro to work though.

Try something like this...

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> Resize_1_OP()<br><br>    <SPAN style="color:#00007F">Dim</SPAN> iEdge <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN><br><br>    <SPAN style="color:#00007F">With</SPAN> Worksheets(3)<br>        .Unprotect Password:="***"<br>        .Range("39:60, 78:99").EntireRow.AutoFit<br>        .Range("39:60, 78:99").RowHeight = 0<br>        <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN><br>        .HPageBreaks(1).Delete  <SPAN style="color:#007F00">'This would error if the sheet had no HPageBreaks</SPAN><br>        <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> 0<br>        .PageSetup.PrintArea = "$A$12:$Q$99"<br>        <br>        <SPAN style="color:#00007F">With</SPAN> .Range("B27:C38,D27:O38,B66:C77,D66:O77")<br>        <br>            .Borders(xlDiagonalDown).LineStyle = xlNone<br>            .Borders(xlDiagonalUp).LineStyle = xlNone<br>            <SPAN style="color:#00007F">For</SPAN> iEdge = 7 <SPAN style="color:#00007F">To</SPAN> 10<br>                <SPAN style="color:#00007F">With</SPAN> .Borders(iEdge)<br>                    .LineStyle = xlContinuous<br>                    .ColorIndex = 0<br>                    .TintAndShade = 0<br>                    .Weight = xlMedium<br>                <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>            <SPAN style="color:#00007F">Next</SPAN> iEdge<br>            <SPAN style="color:#00007F">For</SPAN> iEdge = 11 <SPAN style="color:#00007F">To</SPAN> 12<br>                <SPAN style="color:#00007F">With</SPAN> .Borders(iEdge)<br>                    .LineStyle = xlContinuous<br>                    .ColorIndex = 0<br>                    .TintAndShade = 0<br>                    .Weight = xlThin<br>                <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>            <SPAN style="color:#00007F">Next</SPAN> iEdge<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>        .Protect Password:="***"<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>        <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[ljrezn]

Wow that is much more simplistic and runs much quicker! Thank you for your help and expertise. I still have a lot to learn about writing VBA code.

Thank you!