Prevent Users from switching worksheet

[Elie]

Here is my goal. I have a form which is non modal so that users can
select cells from a chosen worksheet. Users should only be able to
chose cells from that worksheet. Therefore I want to take away the
ability to change worksheets while that form is displayed.

What I have tried.
I figured maybe I could disable the tabs...
Application.CommandBars("Workbook tabs").Enabled = false

But this seems to have no affect on anything. So I resorted to hiding
all of the other worksheets during the form, and redisplaying them
after.

Is there another way? Should I be able to disable the tabs?

Thanks. Please also kindly respond to my private email if you can.

-Ew

 

[John M]

The following code will hide the worksheet tabs from the user. I do not know if this is sufficient to solve your proble

ActiveWindow.DisplayWorkbookTabs = False

 

[JON JON]

Hello Elie

Maybe this is what you need. Right-click on the required sheet, choose
"view code " and then copy and paste the code below. You will need to
change Sheet1 with the name of your sheet and UserForm1 with the name of
your userform. What it does it to re-activate your sheet whenever it is
deactivated while your userform is loaded. Take note that the userform must
be "unloaded" in order for the user to select other sheet.

Private Sub Worksheet_Deactivate()
For Each frm In UserForms
If frm.Name = "UserForm1" Then
ActiveWorkbook.Sheets("Sheet1").Activate
Next frm
End Sub

Hope this help.

Jon-jon

 

[Elie]

ActiveWindow.DisplayWorkbookTabs = False

John M. Thanks. It does just what I want. Hides the tabs. Why do you
think it may not be sufficient.

-Elie

 

[JON JON]

Hello Elie,

Any average user know that you can switch between sheets by ctrl+pageup and
ctrl+pagedown. Have you red my reply to your post?

Regards,

 

[Elie]

Jon Jon,

Thanks. I did see your reply. I am advanced in some parts of excel but
still rather novice in many things. This is my first major excel
project so please bare with me.

I think that your first solution will work for me (well, similar
anyway because I didn't describe my requirements that accurately).

Thanks,
Elie.