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!

 

[ada111]

Hey Rick, just checking back in. I don't get why it isn't working

 

[Rick Rothstein]

Hey Rick, just checking back in. I don't get why it isn't working

It is not working because you changed the range it is applied to from the entire column (which I had to guess at because you had not mentioned the restricted range at that point in time) to a restricted set of cells... you have to change the formula to match the range it applies to. Select the range H11:J33, and then use this formula in the Data Validation formula field...

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

 

[ada111]

Now the box is coming up but it is coming up when I click anywhere. Even when I try to add a new entry or try to do the drop down menu in Column G.

 

[Rick Rothstein]

Now the box is coming up but it is coming up when I click anywhere. Even when I try to add a new entry or try to do the drop down menu in Column G.

Did you include Column G in your selection before you added the formula to the Data Validation dialog box??? If so, you should not have... my instructions were to select H11:J33 (not G11:J33).

 

[ada111]

I only highlighted H11:J33. I did not highlight column G. Is there a macro I could put in my visual basic? Because I don't see Data Validation working

 

[ada111]

I just did this macro and the box pops up everytime I cell is clicked. How can I tweak it to where it only comes up if column G is not filled out but also where the user can click in columns B:F and the box doesn't come up but if they try to go to H:J with G empty, then the box pops up??


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim i As Integer
If Intersect(Target, Range("G11:G33")) Is Nothing And WorksheetFunction.CountA(Range("G11:G33")) <> 33 Then
Application.EnableEvents = False
MsgBox "You Must Complete Column G First", vbCritical
For i = 11 To 33
If Range("G" & i) = "" Then
Range("G" & i).Select
Exit For
End If
Next i
Application.EnableEvents = True
End If
End Sub

 

[ada111]

I just did this macro and the box pops up everytime I cell is clicked. How can I tweak it to where it only comes up if column G is not filled out but also where the user can click in columns B:F and the box doesn't come up but if they try to go to H:J with G empty, then the box pops up??


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim i As Integer
If Intersect(Target, Range("G11:G33")) Is Nothing And WorksheetFunction.CountA(Range("G11:G33")) <> 33 Then
Application.EnableEvents = False
MsgBox "You Must Complete Column G First", vbCritical
For i = 11 To 33
If Range("G" & i) = "" Then
Range("G" & i).Select
Exit For
End If
Next i
Application.EnableEvents = True
End If
End Sub