Macros Box Opens When Running VBA Code

[zobro93]

Hi all,

I'm attempting to Hide Sheet titled "LOW" if B2 is "FALSE", or Unhide if B2 is "TRUE", however when I press 'Run Sub', the Macros Dialog Box pops up. I've attempted to find the answer elsewhere, but to no avail.

CODE AS FOLLOWS:
Private Sub Worksheet_Change(ByVal Target As Range)


If Range("B2").Value = "FALSE" Then Sheets("LOW").Visible = False
Else: If Range("B2").Value = "TRUE" Then Sheets("LOW").Visible = True

End If

End Sub

Thanks All

 

[steve the fish]

Hi. There will be lots of ways to write this but heres one that should work for you:

Private Sub Worksheet_Change(ByVal Target As Range)

myVar = Range("B2").Value

If Not IsError(myVar) Then
    If myVar = False Or myVar = True Then
        Sheets("LOW").Visible = myVar
    End If
End If

End Sub

 

[zobro93]

Hi Steve,

Still doesn't work - I've saved it as macro enabled, but somehow still not actioning. Any ideas?

Cheers,

Z

 

[steve the fish]

Where are you placing the code? You need to right click on the sheet tab. Press view code. Paste the code into the big white space. Close the vba window. Then type false into cell B2. The sheet named Low should be hidden. Type true into B2 and it reappears.

 

[zobro93]

I'm working on Mac & Excel 2011, so going into "Visual Basic Editor" from the Developer Tab, entering the code on the "Database" spreadsheet, where the B2 Cell is referencing to. When I right-click sheet tab, there is no "View Code" - not sure if this is due to Mac.

 

[steve the fish]

Maybe im not sure. Cant help with that im afraid as im on a pc and never used a mac.

 

[Norie]

The code you've posted will be triggered when a change is made on the worksheet, you wouldn't manually execute it via 'Run Sub' or whatever.

When you goto Run Sub and the cursor is not positioned within a sub that can be run manually the Macros dialog will pop up.

 

[zobro93]

Hi Norie, cursor is definitely within the code before I execute "run sub". Questioning if it's because I had placed it without "right-clicking sheet to View Code". When I close it all, and change B2 to False/True, nothing happens

 

[Norie]

If you type TRUE/FALSE in a cell then Excel interpret it as a boolean value, in your code you are looking for the strings 'TRUE' or 'FALSE'.

Try changing the code to this.

Private Sub Worksheet_Change(ByVal Target As Range)


    If Range("B2").Value = False Then
        Sheets("LOW").Visible = False
    ElseIf Range("B2").Value = True Then
        Sheets("LOW").Visible = True
    End If

End Sub

 

[zobro93]

Thanks all for your assistance - Norie, this code worked. Not sure what happened with the others, investigating now. Cheers!