VB crashes when worksheet is hidden or protected

[FiservEFTSalesOps]

Hello fellow posters (It's been a very long time since my last post)

I've put together a dashboard in Excel 2010 that has several worksheets and some beginner VB code. One of the worksheets ("Control") drives many of the dashboard actions. The VB also references this worksheet. I don't want users to change anything on the "Control" sheet, however when I hide it or protect it, it breaks the VB. For you experienced VB'ers, this is probably easy. I've worked long and hard on thsi dashboard and this is the last piece of the puzzle.

Thanks in advance for any help.

 

[JLGWhiz]

Hello fellow posters (It's been a very long time since my last post)

I've put together a dashboard in Excel 2010 that has several worksheets and some beginner VB code. One of the worksheets ("Control") drives many of the dashboard actions. The VB also references this worksheet. I don't want users to change anything on the "Control" sheet, however when I hide it or protect it, it breaks the VB. For you experienced VB'ers, this is probably easy. I've worked long and hard on thsi dashboard and this is the last piece of the puzzle.

Thanks in advance for any help.

I am not familiar with the error message "VB Broken". However, I do know that if you try to make a change on a protected worksheet using VBA, it will most likely throw an error. You first have to unportect the sheet, do your bit, then re-protect the sheet. I imagine the same is true with the hidden sheet for the most part, although I have seen some code that referred to a value on a hidden sheet that did not produce an error. It is easy enough to resolve. If it produces the error, unhide or unprotect as the case may be, then re-hide and re-protect.

 

[FiservEFTSalesOps]

Based on feedback I inserted this into by VB and it works great. Thanks all.

With Sheets(name)
.Visible = True
' code here
.Visible = false
End with

 

[JLGWhiz]

Glad to help
Regards, JLG