Problems with hidden rows

[Davespil]

Hello. Using Excel I created a worksheet that allows other users to enter some data and select data from drop down menus and get a solution. I use worksheet 1 as the user interface and have a bunch of data and formulas on the next worksheet. It works well but I came across two problems and I don't know if they can be fixed. Here they are:

#1. I have the worksheet protected and only the cells that data entry is needed unlocked. I also have a macro that runs when the workbook is opened. It hides certain rows. The problem is a protected worksheet and macros that hide or unhide rows don't work together. Is there a way past that? I really want to limit the cells that the user has access to but I also need certain rows hidden unless the used decides to unhide them (using another macro tried to a button).

#2. If the button that I have that hides/unhides certain rows is clicked while the user's cursor is in one of the cells to be hidden then that row is not hidden. The rest are but Excel won't hide that row if it's clicked on. Is there a way to override that?

I know these are some weird questions but I am trying to lock down this user interface. Thank you in advance.

[p45cal]

1. Check out the UserInterfaceOnly argument of the Protect method:
UserInterfaceOnly Optional Variant True to protect the user interface, but not macros. If this argument is omitted, protection applies both to macros and to the user interface. Use it in your workbook open macro - it's remembered until the workbook is closed so you don't need to unprotect/reprotect sheets to run a macro.
2. Need to see existing code and have a good idea of what's on the sheet - best supply a workbook.

[snb]

The best method to control a user interface is to use a userform.

[Davespil]

1. Check out the UserInterfaceOnly argument of the Protect method:
UserInterfaceOnly Optional Variant True to protect the user interface, but not macros. If this argument is omitted, protection applies both to macros and to the user interface. Use it in your workbook open macro - it's remembered until the workbook is closed so you don't need to unprotect/reprotect sheets to run a macro.
2. Need to see existing code and have a good idea of what's on the sheet - best supply a workbook.

OK, so on the Protect Worksheet menu I checked Format Rows option and now both macros run without problems. Is that what you meant? If so then thank you. I can't post the workbook on here, company policy. Even a completely scrubbed workbook. I just can't share it. Here is a better explanation of the second question:
I have a macro to hide rows 10:20. If I have say cell B14 selected and I hit the button that activates the macro then row 14 will not be hidden. Rows 10-13 and 15-20 are. But because I have cell B14 selected row 14 will not hide. I have to unselect the cell and hit the macro again. I want to know if its possible to avoid that. Make that row hidden even if its selected. Thank you in advance.

[p45cal]

OK, so on the Protect Worksheet menu I checked Format Rows option and now both macros run without problems. Is that what you meant?

No.

[JKwan]

hmm, weird. My rows are all hidden when I select B14. I don't understand why your row 14 is not hidden. I hope this will solve your problem by moving the cursor else where. Give this a go and see if this solves it

    
Range("A1").Select
    Rows("10:20").EntireRow.Hidden = Not Rows("10:20").EntireRow.Hidden

[Davespil]

hmm, weird. My rows are all hidden when I select B14. I don't understand why your row 14 is not hidden. I hope this will solve your problem by moving the cursor else where. Give this a go and see if this solves it

    
Range("A1").Select
    Rows("10:20").EntireRow.Hidden = Not Rows("10:20").EntireRow.Hidden

It worked! Thank you!