VBA Stopped Working

[emacjake]

Hi All

I'm not sure whats happened but i had the below codes written so that if i changed either 'A5' or 'A10' all columns between 2 - 100 would hide except the columns with heading that match 'A5' or 'A10'.

This is a little above me i wrote most of it from this web site and Youtube, and it had been working fine for several months now.

I'm not sure what has changed, i haven't touched the code and have also used it in other workbooks and they've stopped working also,

So when i change these cells just nothing happens?

I have the public function save in it's own module and the Private Sub is saved to each sheet that uses it.

Any help would be greatly appreciated!!






Public Function GetColumnLetter_ByInteger(what_number As Integer) As String
'Converts to column number from a number to a letter!


GetColumnLetter_ByInteger = ""


MyColumn_integer = what_number


If MyColumn_integer <= 26 Then
column_letter = Chr(64 + MyColumn_integer)
End If

If MyColumn_integer > 26 Then
column_letter = Chr(Int((MyColumn_integer - 1) / 26) + 64) & Chr(((MyColumn_integer - 1) Mod 26) + 65)


End If


GetColumnLetter_ByInteger = column_letter


End Function




Private Sub Worksheet_Change(ByVal Target As Range)
'When Cell A5 is changed only columns that have this heading will be displayed


If Target.Address = "$A$5" Then


Dim the_selection As String
Dim product_sales As String


the_selection = Sheet30.Range("A5")
Dim Rep As Integer
For Rep = 2 To 100
the_column = GetColumnLetter_ByInteger(Rep)
product_sales = Sheet30.Range(the_column & "2")
If the_selection = product_sales Then
Sheet30.Range(the_column & ":" & the_column).EntireColumn.Hidden = False
Else
Sheet30.Range(the_column & ":" & the_column).EntireColumn.Hidden = True
End If
Next Rep

End If


'When Cell A10 is changed only columns that have this heading will be displayed


If Target.Address = "$A$10" Then


Dim Outlet_Option As String
Dim Outlet_Choice As String


Outlet_Option = Sheet30.Range("A10")
Dim Rept As Integer
For Rept = 2 To 100
total_column = GetColumnLetter_ByInteger(Rept)
Outlet_Choice = Sheet30.Range(total_column & "1")
If Outlet_Option = Outlet_Choice Then
Sheet30.Range(total_column & ":" & total_column).EntireColumn.Hidden = False
Else
Sheet30.Range(total_column & ":" & total_column).EntireColumn.Hidden = True
End If
Next Rept

End If


End Sub

 

[emacjake]

Just another guess...it's possible that some other macro or action turn off "EnableEvents", and they never got turned back on again.
Try running the following (outside your broken macros, since they may not be triggering at all).

Sub FixEvents()
    Application.EnableEvents = True 'turn events on
End Sub

Again, just a guess, but it might restore order to the universe.
Hope that helps,

Wow I think this worked!! Seems to be working again!!!

Any ideas what i can look for that may be Disabling events in other macro?

 

[Cindy Ellis]

Glad it seems to have kickstarted your macros again!

To find the root cause, search for "EnableEvents = False". Some macros use that to ignore Excel events (worksheet change events like the one you're using, and a variety of others) to have better control over a given macro. A well-written macro we re-enable events when it is complete, but if the macro is interrupted, events may not get turned back on.

Hope that helps,

 

[emacjake]

Now i think of it i was trying to write another macro to close one particular workbook when left inactive for a certain period of time, I've deleted this as i couldn't get it to work, but i think that it may of had disable.events in it. hence why i couldn't find the problem.


Once again I just wanted to say a huge thank you to you and everyone one this forum, for all the help you give to all of us that aren't so well versed in the beast that is excel.