VBA: Created Mouseover Code that works. Need to Show/Hide Columns based on Mouseover

Question

I've created a mouseover macro, that when moused over certain cells, changes the value in the linked cell, [Expanded_Rollover], to either be "View Expanded Details" or "View Data Filters". From this linked cell, based on either of the above values, I want to create code that either hides or reveals columns "O:S" of my dashboard. I have a code that works for hiding and revealing the columns alone, but I can't link the two codes to work at once on just the mouseover. PLEASE HELP!!

Public Function ExpandedRollover(Expanded_Options As String)

    'Cell Expanded Rollover = Moused over cell value in Expanded_Options Range - Values are 1 or 2
    [Expanded_Rollover] = Expanded_Options

    'Call Private Sub located on Dashboard Sheet
    Call ExpandedDetailsShowHide

End Function


Private Sub ExpandedDetailsShowHide()
Dim Expanded_Rollover As Integer

If [Expanded_Rollover] = 1 Then

ActiveSheet.Columns("O:S").Select
    If Selection.EntireColumn.Hidden Then
    ActiveSheet.Columns("O:S").Select
    Selection.EntireColumn.Hidden = True
    ActiveSheet.Range("E4").Select
    Else:

    If [Expanded_Rollover] = 2 Then
    ActiveSheet.Columns("O:S").Select

        If Selection.EntireColumn.Hidden Then
        ActiveSheet.Columns("O:S").Select
        Selection.EntireColumn.Hidden = False
        ActiveSheet.Range("E4").Select
        End If


    End If


End Sub

You should try to [avoid using select in VBA](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros). — ChipsLetten, Aug 11 '15 at 21:23

score 0 · Answer 1 · answered Aug 11 '15 at 20:10

0

You call the pieces of code one after the other.

[Expanded_Rollover] = "something"
Call HideUnhideColumns

End Function

Private Sub HideUnhideColumns
    ' reads the linked cell value
    ' hides/unhides columns
End Sub

answered Aug 11 '15 at 20:10

ChipsLetten

2,923
1
11
28

I have it setup like this, and the mouseover code works for changing my reference cell value (simply 1 or 2 based on mouse location within a range), but I can not get the Private Sub to so anything based on the [Expanded_Rollover] Cell changing value. Please see pictures. http://imgur.com/aS79Azq,PDukh8W – Chase Sanderson Aug 11 '15 at 20:42
Can you add your code to your question. That is better than links to an image. – ChipsLetten Aug 11 '15 at 20:43
Yes, my bad. First post, so I'm new to this. I have the MouseOver located on a Module and the Private Show Hide Sub located on the Dashboard tab if that makes a difference. See original post comment for current code. – Chase Sanderson Aug 11 '15 at 20:47
The `Private` in `Private Sub` means the proc is only accessible to code in the same module. Change it to `Public Sub` to use from other modules. – ChipsLetten Aug 11 '15 at 21:21
With the Public Sub moved to another tab (Dashboard) tab, I now get the error "Sub or Function not defined". Any idea what I define it as? – Chase Sanderson Aug 11 '15 at 21:25
Do you know about using F8 and breakpoints to step through code? If not, [see here](http://www.cpearson.com/Excel/DebuggingVBA.aspx). Use those to see whether the hide/unhide code runs. – ChipsLetten Aug 11 '15 at 21:29
I do not. Reading that site now. – Chase Sanderson Aug 11 '15 at 21:30

VBA: Created Mouseover Code that works. Need to Show/Hide Columns based on Mouseover

1 Answers1