Hi everyone, I'm a VBA/Macro newb and with the help of some folks on here and some friends I've built the following 4 Macros:
They work perfectly with one exception: When a the BlankFinder finds a Blank cell, the MsgBox pops up but continues through the loops and when using RunAllMacros not only does it continue through the loops but Rename and Hide also run. This typically isn't a problem but if more than one C is left blank then you get a VBA 400 error. I'm trying to user proof this because people in my office are leery of Macros/VBA so broken macro/vba pop ups they don't understand will cause them to quit using it entirely.
What I'd like to happen is that when BlankFinder finds a blank cell it gives an MsgBox and stops running. In RunAllMacros if Blankfinder finds a blank I want it to end all process, so it doesn't continue through the loops and doesn't run Rename or Hide. I'm sure this is possible I just can't figure out how with my limited knowledge.
Any advice would be very much appreciated.
Quick note, as I said I had help with these from here and elsewhere so I don't have answers as to why i did it this way vs. that other than it worked. I'm not married to any of these solutions. Also in the Hide Sub it says 3 to 5 instead of 3 to 17 because I'm still working on the mechanics of the PartsCatalog and so I've only created sheets for 1, 2 and 3 so I can test these out. There will eventually be 15 pairs of sheets but currently there are only 3. Thanks again
Code:
Sub BlankFinder() 'Prevents blank names in PowerPackNames. Blanks cause follow-on Macros to fail and cannot be completely accounted for in Data Validation
For c = 3 To 17 'sets C as variable below for Column # for Col C (3) through Q (17)
If Cells(3, c) = "" Then 'if Cells C3-Q3 (determined by C variable) are blank then MsgBox.
MsgBox "Power Pack Names Cannot Be Blank"
ElseIf IsEmpty(Cells(3, c)) Then 'If cells C3-Q3 (determined by C variable) are empty then MsgBox. Not sure diff between Blank and Empty. Covering Bases.
MsgBox "Power Pack Names Cannot Be Blank"
Else: End If 'If cell is not blank nor empty then do nothing.
Next c 'for each "C" (the cell in each column determined by the C variable) this loop is run. If the cell is not blank or empty it moves on to the next C.
End Sub
Sub Rename() 'This Macro renames each worksheet with the name in cell A1 of that sheet.
'A1 is formula that throughputs the Power Pack Name from Step1 C3-Q3. Sheet name restirctions are controlled in those cells.
'string length, special characters and duplicates are controlled using Data Validation forumlas.
'Blanks are controlled with the BlankFinder Macro above.
Dim ws As Worksheet 'sets ws as shorthand for worksheet.
For Each ws In Sheets 'Tells the Macro to run for ALL worksheets.
ws.Name = ws.Range("A1") 'Since this runs for ALL worksheets, this means that Step1 and Admin also have their titles in A1. in Step1 hidden by Button.
Next ws 'Once a sheet is renamed to whatever is in A1 moves on to next Worksheet.
End Sub
Sub Hide() 'This macro hides worksheets that are not currently in use by the user.
For c = 3 To 5 'This sets the Variable C. C is the column number in the cell address used below. C provides the range of cells the macro will work for.
'In practicle terms this sets what sheets are being effected by this macro based on matching the sheet names to the names in C3-Q3.
'These sheets are to be hidden if the dropdown in C2-Q2 for that sheets corresponding column is set to "No" and made visible if set to "Yes"
'Ensure that the To number above is set to you column number of the last cell in Power Pack Names that has a matching Worksheet set.
'This also means that the worksheet naming convention must match that stated below. This step is handled by the Rename Macro above.
If Cells(2, c) = "No" Then 'If drop down in Row 2 for the corresponding column C is set to No
Worksheets(CStr(Cells(3, c) & "Step2")).Visible = False 'Then hide worksheet that matches the naming convention in that column row 3 concatinated with "Step2"
Worksheets(CStr(Cells(3, c) & "PartsCatalog")).Visible = False 'Then hide worksheet that matches the naming convention in that column row 3 concatinated with "PartsCatalog"
'This also means that if the worksheet naming conventions change from Step2 and PartsCatalog the macro needs to change to match.
Else
Worksheets(CStr(Cells(3, c) & "Step2")).Visible = True 'Then unhide/leave visible worksheet that matches the naming convention in that column row 3 concatinated with "Step2"
Worksheets(CStr(Cells(3, c) & "PartsCatalog")).Visible = True 'Then unhide/leave visible worksheet that matches the naming convention in that column row 3 concatinated with "PartsCatalog"
End If
Next c 'Once this is done move on to next cell as determined by C variable
End Sub
Sub RunAllMacros() 'This Macro is used to run all the above macros, in order.
'This is done to try and prevent any error states in the Hide Macro by making sure that any naming issues hare fixed by the naming macro first
'This also prevens the Renaming macro from hitting an error state from trying to rename worksheets based on blank titles, which would cause duplicate worksheet names
'This is prevented by running the BlankFinder immediately before hand.
'The blank finder combined with Data Validation rules w/in the cells should prevent the vast majority of error states.
BlankFinder
Rename
Hide
End SubThey work perfectly with one exception: When a the BlankFinder finds a Blank cell, the MsgBox pops up but continues through the loops and when using RunAllMacros not only does it continue through the loops but Rename and Hide also run. This typically isn't a problem but if more than one C is left blank then you get a VBA 400 error. I'm trying to user proof this because people in my office are leery of Macros/VBA so broken macro/vba pop ups they don't understand will cause them to quit using it entirely.
What I'd like to happen is that when BlankFinder finds a blank cell it gives an MsgBox and stops running. In RunAllMacros if Blankfinder finds a blank I want it to end all process, so it doesn't continue through the loops and doesn't run Rename or Hide. I'm sure this is possible I just can't figure out how with my limited knowledge.
Any advice would be very much appreciated.
Quick note, as I said I had help with these from here and elsewhere so I don't have answers as to why i did it this way vs. that other than it worked. I'm not married to any of these solutions. Also in the Hide Sub it says 3 to 5 instead of 3 to 17 because I'm still working on the mechanics of the PartsCatalog and so I've only created sheets for 1, 2 and 3 so I can test these out. There will eventually be 15 pairs of sheets but currently there are only 3. Thanks again
