VBA Avoid hard-coding values to call macros associated with values entered in a cell

[ave10]

I have values in column D of a sheet (Sheet2) that are derived from a formula of longer values that are automatically entered in column C thru a double-click. So, the values in columnd D is a part taken from the values in column C. On a separate sheet (Sheet5), I have a list of values in the C column of that sheet. If any of the values in column D of Sheet2 equal any of the values in column C of Sheet5, I want the cell in the F column of Sheet2 to turn red. Once the F column cell in Sheet2 is red, depending on the value in column D, I want the red colored cell to become clickable which will take the user to a different sheet listing reference values associated with the value in column D. The code below does just that. **However** I would like to avoid hard coding the different values in anticipation that some of the values which require a reference code will be added or taken away from the list in column C of Sheet5.

Option Explicit
    
    Private Sub Worksheet_Change(ByVal Target As Range)
    
        Dim c As Range: Set c = Union(Range("D7:D446"), Range("F7:F446"))
        Dim CellF As Range, CellD As Range, Cell As Range
    
    If Not Application.Intersect(c, Range(Target.Address)) _
               Is Nothing Then
    
            Set CellF = Range("F" & Target.Row)
            Set CellD = Range("D" & Target.Row)
    
            'Dim refCodes As Variant
            'refCodes = Sheet5.Range("C1:C17").Value
    
            If CellF.Value <> "" Then
                CellF.Interior.ColorIndex = 0


                'If CellD.Value = refCodes.Value Then
                    'CellF.Interior.ColorIndex = 3


                Select Case CellD.Value
                             Case "0GP", "0MM", "FEST", "IEDU", "ONLC", "PART", "PRDV", "SPPR", "DANC", "LFLC", "MEDA", "CCH", "POUBL", "GA01", "GA17", "GA99", "REDV"
                        CellF.Interior.ColorIndex = 3
                Case Else
                    CellF.Interior.ColorIndex = 0
            End Select
            End If
            End If
    'End If
    End Sub
    
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
      Set sourceRange = Nothing 
      If Target.Column = 6 And Target.Cells.Count = 1 And Target.Interior.ColorIndex = 3 Then
          Set sourceRange = Target 
        
          Select Case Target.Offset(0, -2).Value2
            Case "0GP": gotoref1
            Case "0MM": gotoref2
            Case "FEST": gotoref3
            Case "IEDU": gotoref4
            Case "ONLC": gotoref5
            Case "PART": gotoref6
            Case "PRDV": gotoref7
            Case "SPPR": gotoref8
            Case "DANC": gotoref9
            Case "LFLC": gotoref10
            Case "MEDA": gotoref11
            Case "CCH": gotoref12
            Case "PUBL": gotoref13
            Case "GA01": gotoref14
            Case "GA17": gotoref15
            Case "GA99": gotoref16
            Case "REDV": gotoref17
          End Select
    End If
    
    End Sub

I thought the best way to go about avoiding hard-coding was to create an array. In the first sub you'll see the commented out snip-its of code, I tried to do that but, I am unsure of how I can call the different macros since they are associated with certain values.


Thanks in advance for any help!

 

[ave10]

Hey Eric,

Sorry if there is any confusion.

That is exactly what my ref_list sheet looks like. But in column D, the values are just the text "Ref1", "Ref2", etc. So, when I added that MsgBox it does pop up and return the string "Ref1" if the value in column D is "0GP "and "Ref2" if the value in column D is "0MM", and so on. But, once I hit "Ok" on the MsgBox, it takes me to the sheet "ref_list" rather than the sheet "Ref1", "Ref2", Ref3, etc. as needed.

Is there a way I can set the rows in column D equal to a sheet like '=Sheet(Ref1) or something. Or perhaps but a hyperlink in column D to the actual sheets Ref1, Ref2, Ref3, etc?

I hope that clarified everything. I'll be happy to provide more detail if you need. Thanks again for your help, I sincerely appreciate it.

 

[Eric W]

The Sheets(MySheet).Activate line should take the value (Ref1 or whatever) and go to that sheet. My latest guess is that it really does go to that sheet, but you have other event handlers that take over after that and activate the ref_list sheet. You can test this. Go to the SelectionChange code and put a breakpoint on the top line by pressing F9. Then go back to your worksheet, click on one of the F cells, and it should take you back to the code. Single step through it by pressing F8. When you get to the Activate line, it should activate the sheet, but if another handler takes over, the editor will show you. It that's the case, you can disable and reenable the events like this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim MySheet As String

    On Error GoTo Done:
    If Target.Column = 6 And Target.Cells.Count = 1 Then
        MySheet = WorksheetFunction.VLookup(Target.Offset(0, -2).Value, Sheets("ref_list").Range("$C$1:$D$17"), 2, 0)
        Application.EnableEvents = False
        Sheets(MySheet).Activate
        Application.EnableEvents = True
    End If
Done:
End Sub

 

[ave10]

Hi Eric,


I did what you outlined and when I hit F8 and got down to the 'Sheets(MySheet).Activate', it doesn't activate. It does noting, essentially. I do have the the Ref1, Ref2, Ref3, Ref4, etc. sheets hidden so the users who use this workbook do not see the various different sheets. Would that explain why this is happening?

Here is the code I have in my sheet:

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim MySheet As String


    On Error GoTo Done:
    If Target.Column = 6 And Target.Cells.Count = 1 Then
        MySheet = WorksheetFunction.VLookup(Target.Offset(0, -2).Value, Sheets("ref_list").Range("$C$1:$D$17"), 2, 0)
        Sheets(MySheet).Activate
    End If
Done:
End Sub

Thanks again!

 

[Eric W]

That's exactly what is happening!

We need to unhide it before we can activate it:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim MySheet As String

    On Error GoTo Done:
    If Target.Column = 6 And Target.Cells.Count = 1 Then
        MySheet = WorksheetFunction.VLookup(Target.Offset(0, -2).Value, Sheets("ref_list").Range("$C$1:$D$17"), 2, 0)
        Sheets(MySheet).Visible = True
        Sheets(MySheet).Activate
    End If
Done:
End Sub

 

[ave10]

Awesome! That seemed to have worked. There is one slight adjustment I was hoping to make.

I only want one sheet visible at a time. So, when I navigate to those "Ref#" sheets and when I go back to my original sheet, I'd like them to turn back to being hidden. Would you be able to help me with that?

I've tried just adding Sheets(MySheet).Visible = False after the End If but, that didn't seem to work.

Thanks again, Eric!

 

[Eric W]

You pretty much have to leave that subroutine unchanged, you can't rehide the sheet until the user is done with it. Try adding another event handler in the same sheet module, put this code after the SelectionChange event:

Private Sub Worksheet_Activate()
Dim c As Range
        
    On Error Resume Next
    For Each c In Sheets("ref_list").Range("D1:D17")
        Sheets(c.Value).Visible = False
    Next c
End Sub

The SelectionChange event will take them to another sheet, but when they come back to the main sheet, the Activate event will trigger, and it will rehide any sheet on the list on ref_list. This works if they manually select the sheet tab to go back, and will probably work if they go back because of a macro. I can't say for sure because I don't know what's in the macro.

 

[ave10]

That seemed to work. Thanks so much! I've been working on this for some time and you've been an incredible help.

If I want to hide sheets while using others, do you suggest I write another event handler in the sheet and specifically write which sheets I want to hide?

I, essentially, want to hide ALL sheets that I am not currently using that way only one sheet is visible at any given time. So, would writing an additional event handler in each sheet, saying to hide all other sheets satisfy that?

Thanks again, Eric!

 

[Eric W]

Seems like an odd way to do things, but sure, you could put this event handler in the ThisWorkbook tab:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim MySh As Worksheet

    For Each MySh In ActiveWorkbook.Worksheets
        If MySh.Name <> Sh.Name Then MySh.Visible = False
    Next MySh
    
End Sub

or put this one in ALL of your sheet tabs:

Private Sub Worksheet_Activate()
Dim MySh As Worksheet

    For Each MySh In ActiveWorkbook.Worksheets
        If MySh.Name <> ActiveSheet.Name Then MySh.Visible = False
    Next MySh
    
End Sub

Note that either option should replace the code from post #16. You don't want two handlers doing the same thing.

 

[ave10]

Thanks a lot, Eric! That seemed to do the trick. I appreciate your help with this.

I do have one Final question I was hoping you could help me with. You've really been a great help so far.

As I'm sure you know, in the sheet JE, the F column become clickable if the D cell has certain values. The sheets that are called upon are the sheets Ref1, Ref2, Ref3, etc. If I have a value in row 7 that doesn't require a value be placed in the F column (F7) and a value in row 8 that does require a value in column F (F8), when I double-click on a value in the Ref# sheets, it will be populated in F7, rather than be populated in F8. Do you know of an adjustment I can make to my code in those sheets for when I double-click a value in column A of the Ref# sheets, that they populate the row I clicked in, rather than going to the first cell?

Here is the code I have in all of my Ref# sheets:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)


If Target.Column = 1 Then
    For j = 7 To 447
        If Worksheets("JE").Range("F" & j).Value = "" Then
             Worksheets("JE").Range("F" & j).Value = ActiveCell.Value
             Worksheets("JE").Activate
             Exit For
        End If
     Next j
End If


'Cancel = True
Sheets("JE").Visible = False
'Dim sheet As Worksheet
'Set sheet = Sheet3
Sheets("acct_codes").Visible = False
Application.ScreenUpdating = True


End Sub

 

[ave10]

Also, there is one more thing I wanted to add.

With this code, once I double-click a value in the A column of the Ref# sheets, it does not take me back to the JE sheet, which I would like it to do. Sorry I forgot that and thanks again!