Pop up restriction box

[ada111]

Hi, I am needing a way to have a pop up come up restricting a user from moving on until a certain column is filled. I have a spreadsheet for a doctor's office and it has columns B-J. Columns B-F need to stay untouched. But when the user gets to column G, I need a macro where the user CANNOT move on unless column G is filled. I have a drop down box that lists providers/users and I need a pop up restriction that says "You Must Fill Out Providers/Users First".

Any help will be amazing. Thank you all in advance!

 

[Rick Rothstein]

Can you explain what you mean by "Columns B-F need to stay untouched"? How is the user interacting with the worksheet (I trying to figure out how they get on the row in the first place in order that they should be locked into Column G on that row)?

 

[ada111]

I have columns B-J and what I am wanting is a box that pops up if a user tries to go to column H-J and did not choose an option in the drop down menu in column G. So users can put in information freely but if they try to go to columns H-J and column G is not filled out first, then I want a box to come up saying "You Must Enter In Provider/User First"

 

[Smitty]

It sounds like you might be better off using a user form.

 

[Rick Rothstein]

I have columns B-J and what I am wanting is a box that pops up if a user tries to go to column H-J and did not choose an option in the drop down menu in column G. So users can put in information freely but if they try to go to columns H-J and column G is not filled out first, then I want a box to come up saying "You Must Enter In Provider/User First"

You can use Data Validation to restrict the user from entering any values in cells H:J unless Column G on that row is filled in first... would that be acceptable? If so...

1) Select Columns H:J

2) Click the "Data Validation" button on the Data tab ("Data Tools" panel).

3) Select "Custom" from the "Allow" drop-down, make sure the "Ignore blank" checkbox is not checked and put this formula in the "Formula" field...

=LEN(INDEX(G:G,ROW(H1)))

4) Click the "Error Alert" tab and put a message in the "Error message" field; something like this maybe... "You must fill out Column G for this row first!"

5) OK your way back to the worksheet and you are done

 

[ada111]

Hey Rick! I did the data validation and it worked for one cell then I started clicking in any cell in H:J and it didn't come up. I know that in H11:J33 is the section that I need in the code. So if column G is not filled out and the user tries to type in cells H11:J33, then the box comes up.

 

[Rick Rothstein]

Hey Rick! I did the data validation and it worked for one cell then I started clicking in any cell in H:J and it didn't come up. I know that in H11:J33 is the section that I need in the code. So if column G is not filled out and the user tries to type in cells H11:J33, then the box comes up.

Sorry, I forgot to lock the Column G references. Use this formula instead...

=LEN(INDEX($G:$G,ROW(H1)))

 

[ada111]

Hey Rick, that didn't work either. Now the box isn't coming up at all.

 

[Rick Rothstein]

Hey Rick, that didn't work either. Now the box isn't coming up at all.

It works for me. Why don't you clear the existing validation that you have (there is a "Clear All" button on the validation dialog box) and start the process over again using my corrected formula instead.

 

[ada111]

I cleared all the validations and then highlighted H11:J33 and did data validation then custom (unchecked ignore blanks) then did =LEN(INDEX($G:$G,ROW(H1))) and did the error message and clicked ok. But nothing happened. I tested it, and the box never came up.