Please help with compressing multiple Private Subs

[heyitslujan]

Hi All,

Thanks for checking out my question. I've been asked to create a button that only appears based on 3 of 4 values available from a dropdown box(K6). The values are "REMOVE", "ADD ON" and "REPLACE EXISTING". Here's what I have so far. I can get it to work for one value like, "REMOVE", but do not know how to write this to work for all 3 approved values.

Private Sub Worksheet_Change(ByVal Target As Range)
'ActiveX button
If Range("K6").Value = "ADD ON" Then
ActiveSheet.CommandButton1.Visible = True
Else
ActiveSheet.CommandButton1.Visible = False
End If
End Sub

 

[V_Malkoti]

You can change your If statement as

If Range("K6").Value = "ADD ON" Or Range("K6").Value = "REMOVE" Or Range("K6").Value = "REPLACE EXISTING" Then
 ' Do work
End If

 

[heyitslujan]

Thank you sir! Worked great. Though, I did have to remove the "'Do work" statement for some reason. Here's what ended up working.

Private Sub Worksheet_Change(ByVal Target As Range)'ActiveX button
If Range("K6").Value = "ADD ON" Or Range("K6").Value = "REMOVE" Or Range("K6").Value = "REPLACE EXISTING" Then
 ActiveSheet.CommandButton1.Visible = True
Else
    ActiveSheet.CommandButton1.Visible = False
End If
End Sub

You can change your If statement as

If Range("K6").Value = "ADD ON" Or Range("K6").Value = "REMOVE" Or Range("K6").Value = "REPLACE EXISTING" Then
 ' Do work
End If

 

[V_Malkoti]

My bad. That was just a VBScript comment - a comment in VBScript is preceded by a single quote mark. I should've mentioned that.

 

[heyitslujan]

Wait, that's like some cool cue to proceed with what I orginally had? Aw man! Did I blow that one. One day I'll be one of the cool kids, one day

​

My bad. That was just a VBScript comment - a comment in VBScript is preceded by a single quote mark. I should've mentioned that.

 

[V_Malkoti]

Well a comment is basically a non-executable line in your code. You can document your code statements using comments. That comes handy when you come back to a complicated code after a long time or if you hand over the code to someone else.

Private Sub Worksheet_Change(ByVal Target As Range)'ActiveX button
[COLOR=#008000]    ' Code to show or hide the button
    ' Approved values to show button are "REMOVE", "ADD ON" and "REPLACE EXISTING"
[/COLOR]
    If Range("K6").Value = "ADD ON" Or Range("K6").Value = "REMOVE" Or Range("K6").Value = "REPLACE EXISTING" Then
[COLOR=#008000]        ' Approved keyword in the cell; Show the button [/COLOR]
        ActiveSheet.CommandButton1.Visible = True
    Else
[COLOR=#008000]        ' The cell does not have approved keyword; Hide the button [/COLOR]
        ActiveSheet.CommandButton1.Visible = False
    End If
End Sub