200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
Hi everyone I'm working on a larger macro to help manage a standardized form and I've got a lot done so far but I need to check a cell to see if it equals a certain value and then hide the current worksheet if that cell does equal that value or show a message box with an error message.
I've kind of been assembly the macro in peices but am no expert with programming.
I'm greatful for any help
macrubco answered your question
I just want to add that if you want an error message you could use msgbox like this
Msgbox "You could not do that right now"
So the macro could be
(You should change everything in italic bold)
Sub HideSheetIfA1 ()
If Range("A1") = 0 then
Sheet1.Visible = False
Msgbox "You could not do that right now"
End if
End sub Note: you should specify sheet for range ("A1") if the range is not in the active sheet like this
Sheet2.range ("A1")
And sheet1 (in the macro) and sheet2 could be replaced with Sheets (1) to specify sheet by its number or with Sheets ("SheetName") to specify it by its name
Use the visible property of the worksheet.
Sheet1.Visible = FalseLead this from an If Statement, e.g.
If Range("A1") = (selected number) then
Sheet1.Visible = False
Else.......
This assumes A1 is your determining cell -set this to your need.
I have used Sheet.CodeName, which prevents code failing if a sheet name is changed, or a sheet inserted and the number changed. Only applies to the current workbook.