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

 

[StephenCrump]

Just a quick guess ...

You are hiding/unhiding columns based on values of product_sales in Sheet30 row 2, and Outlet_Choice in Sheet30 row 1

If your code was working, and now isn't, have you perhaps inserted a blank line at the top of Sheet30?

Next suggestion would be to put in a breakpoint in Sub Worksheet_Change and step through when triggered to see what's happening/not happening. It's a little hard for us to know this without seeing your workbook.

Also, your code could be written more succinctly, e.g. it is easy to work with column number rather than using GetColumnLetter_ByInteger to convert to a letter.

 

[HotRhodium]

Ok 2 questions is there an error or just no workey? Do other macros in other Workbooks still work or are all old macros broken? Microsoft put out an update that killed some old macros(I am oversimplifying this intentionally) but there is a fix ill look it up if its what your looking for else if there is an error than there are issues with the code that to be honest I did not read yet. Sorry.

 

[emacjake]

Just a quick guess ...

You are hiding/unhiding columns based on values of product_sales in Sheet30 row 2, and Outlet_Choice in Sheet30 row 1 , Either one (One is a date the other is an outlet) depending on if you change A5 or A10

If your code was working, and now isn't, have you perhaps inserted a blank line at the top of Sheet30?, Nope

Next suggestion would be to put in a breakpoint in Sub Worksheet_Change and step through when triggered to see what's happening/not happening. It's a little hard for us to know this without seeing your workbook.

Also, your code could be written more succinctly, e.g. it is easy to work with column number rather than using GetColumnLetter_ByInteger to convert to a letter.

I'm not quite sure how to change this but do you not need letters to work with the 'Range' Function

 

[emacjake]

Ok 2 questions is there an error or just no workey? Do other macros in other Workbooks still work or are all old macros broken? Microsoft put out an update that killed some old macros(I am oversimplifying this intentionally) but there is a fix ill look it up if its what your looking for else if there is an error than there are issues with the code that to be honest I did not read yet. Sorry.

No error, just nothing? and yes other macros are still working, i don't think i've changed the file format or anything.

 

[StephenCrump]

It was just a guess.

Two suggestions (given we can't see your workbook):

1. Put in a breakpoint and step through your Sub Worksheet_Change code, assuming it is being triggered. Are the line by line results what you expect?

2. Post your workbook (assuming no confidential data, IP etc)

 

[emacjake]

Now i'm sounding like a real rookie but how do i post an example of my workbook to this thread?

 

[HotRhodium]

I cant find the fix I had on my last computer sorry but I believe your code may be ok if the same macro stopped working in multiple workbooks at the same time, was running fine and is just not running(No errors, error handlers etc). You could try a "VBA Decompiler" should make VBA recompile and force excel to ask permission to run the code again. This was a while ago so I hope im not sending on a wild goose chase but fyi the decompiler I have is an awesome tool I use very often here is a link.

VBA Code Decompiler and Compactor

Sorry in advance for it not working LOL

 

[Cindy Ellis]

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,

 

[StephenCrump]

Now i'm sounding like a real rookie but how do i post an example of my workbook to this thread?

1. You can't attach a workbook directly. Instead, upload to a third party provider, e.g. box.com, and post the link on this forum.

2. See Part B of the Forum Guidelines for various ways to post a screenshot: http://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html

Obviously 1 would be more helpful for your current query, if that's possible.