excel vba codes user form

Question

I have created a user form for my work book. I would like the user to navigate their way around the workbook using the button on this user form. Is there any code that I can use to limit the user by only allow them to click on buttons on the user form?.

Answer 1

1. Lock the cells to prevent Selection or Modify
2. Protect the worksheet
3. Have the UserForm code:

a. unprotect the worksheet
b. unlock the cell
c. update the cell
d. relock the cell
e. reprotect the worksheet.

Answer 2

Here is how you will write your code to make it so your userform appears as soon as the workbook is open and then closes the entire form once it is closed, which will basically keep the user to only using the userform.

Keep in mind you can't edit VBA while a userform is open so be careful not to trap yourself out of editing code. Maybe you can have a separate button or something for this reason.

1) Create your form. Press Alt F11 to view VBA. Click Insert > Userform as I am sure you have figured out already. Do whatever you want with the form for the rest of your functionality.

2) Double click on the form in VBA view to view the code for it. Here we will insert our close statement which will close the workbook upon close of the form:

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)

ThisWorkbook.Close SaveChanges:=False

End Sub

3) Now we need to make it so the userform appears immediately upon opening the workbook. Double click on "ThisWorkbook" in your tree view for VBA. Paste this code:

Private Sub Workbook_Open()
UserForm1.Show
End Sub

Hope this helps you along your way.