macro to hide worksheet on button click if a cell equals a certain value


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



Selected Answer

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 = False

Lead this from an If Statement, e.g.

If Range("A1") = (selected number) then
Sheet1.Visible = False

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.



Please put CODE tags around your code. To do that, edit your post, select your code and click the CODE button. 

macrubco, I went ahead and added them for you so you could see what it looks like. If you click the CODE button when you make your post, these tags will appear so you don't have to type them.
don (rep: 1247) Jun 29, '16 at 12:32 pm
Thanks for the help! I went with MRVMV's answer just because he explained a bit more but yours was helpful too! If I had the privileges I would vote both of the answers up.
ronc Jun 29, '16 at 2:14 pm
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login