Orderly Validation Against Separate Ranges
 

Hello;

I would like to check the values in 3 cells, each against a separate range
of values, before proceeding with the computation. (And not using Data
Validation Lists).

Suppose the 3 cells to be checked, in the order presented, a
.... C9, C10, and C11
and their validation ranges a
.... A1:A5, B1:B6, and C1:C7 respectively.

I would appreciate your help to include in the VBA code something like:
IF Range("C9").Value < 'any value in the Range("A1:A5") Then
MsgBox "You must Change C9 value"
' don't exit this IF until you fix the value in C9
Enf IF
IF Range("C10").Value < 'any value in Range("B1:B6") Then
MsgBox "You must Change C10 value"
' don't exit this IF until you fix the value in C10
Enf IF
IF Range("C11").Value < 'any value in Range("C1:C7") Then
MsgBox "You must Change C11 value"
' don't exit this IF until you fix the value in C11
Enf IF
'continue with the program

Thank you.

Orderly Validation Against Separate Ranges
 

Many Thanks

i've been able to learn something here. code may be a little sloppy
(could be shortened i guess) but here goes

Sub CheckValues()
Dim res As Variant:
For i = 1 To 3
If i = 1 Then
ary = Range("A1:A5"): checkcell = [C9]
End If
If i = 2 Then
ary = Range("B1:B6"): checkcell = [C10]
End If
If i = 3 Then
ary = Range("C1:C7"): checkcell = [C11]
End If
res = Application.Match(checkcell, ary, 0)
If IsError(res) Then
MsgBox "You must change value of " & checkcell
Exit Sub
End If
Next i
End Sub

please let me know what you think, and if anyone else has improvements
on this please feel free to post them, it's always good to improve

somethinglikeant






monir wrote:
Hello;

I would like to check the values in 3 cells, each against a separate range
of values, before proceeding with the computation. (And not using Data
Validation Lists).

Suppose the 3 cells to be checked, in the order presented, a
... C9, C10, and C11
and their validation ranges a
... A1:A5, B1:B6, and C1:C7 respectively.

I would appreciate your help to include in the VBA code something like:
IF Range("C9").Value < 'any value in the Range("A1:A5") Then
MsgBox "You must Change C9 value"
' don't exit this IF until you fix the value in C9
Enf IF
IF Range("C10").Value < 'any value in Range("B1:B6") Then
MsgBox "You must Change C10 value"
' don't exit this IF until you fix the value in C10
Enf IF
IF Range("C11").Value < 'any value in Range("C1:C7") Then
MsgBox "You must Change C11 value"
' don't exit this IF until you fix the value in C11
Enf IF
'continue with the program

Thank you.

Orderly Validation Against Separate Ranges
 

One way:

Dim myMsg as string
mymsg = ""
with worksheets("sheet9999")
If application.countif(.range("a1:a5"),.range("C9").v alue) = 0 then
mymsg = "C9"
end if
If application.countif(.range("b1:b6"),.range("C10"). value) = 0 then
mymsg = mymsg & " " & "C10"
end if
If application.countif(.range("c1:c7"),.range("C11"). value) = 0 then
mymsg = mymsg & " " & "C11"
end if
if mymsg = "" then
'keep going
else
msgbox "Please fix these cells:" & vblf & trim(mymsg)
exit sub
end if

monir wrote:

Hello;

I would like to check the values in 3 cells, each against a separate range
of values, before proceeding with the computation. (And not using Data
Validation Lists).

Suppose the 3 cells to be checked, in the order presented, a
... C9, C10, and C11
and their validation ranges a
... A1:A5, B1:B6, and C1:C7 respectively.

I would appreciate your help to include in the VBA code something like:
IF Range("C9").Value < 'any value in the Range("A1:A5") Then
MsgBox "You must Change C9 value"
' don't exit this IF until you fix the value in C9
Enf IF
IF Range("C10").Value < 'any value in Range("B1:B6") Then
MsgBox "You must Change C10 value"
' don't exit this IF until you fix the value in C10
Enf IF
IF Range("C11").Value < 'any value in Range("C1:C7") Then
MsgBox "You must Change C11 value"
' don't exit this IF until you fix the value in C11
Enf IF
'continue with the program

Thank you.

--

Dave Peterson

Orderly Validation Against Separate Ranges
 

Hi somethinglikeant;
Excellent approach!... There's a tiny problem, however, associated with the
Exit Sub in the Error Handling section of your code.
In situations where the error custom message is displayed and acknowledged
by pressing OK, the computation continues and does not wait for the
referenced value in the relevant/focused cell to be fixed first. As a
result, numerous compiler and run-time strange errors appear!!
You might have noticed from my initial post that I commented in each IF:
'don't leave this IF block until the referenced wrong value in C? is fixed.

Regards.


"somethinglikeant" wrote:

Many Thanks

i've been able to learn something here. code may be a little sloppy
(could be shortened i guess) but here goes

Sub CheckValues()
Dim res As Variant:
For i = 1 To 3
If i = 1 Then
ary = Range("A1:A5"): checkcell = [C9]
End If
If i = 2 Then
ary = Range("B1:B6"): checkcell = [C10]
End If
If i = 3 Then
ary = Range("C1:C7"): checkcell = [C11]
End If
res = Application.Match(checkcell, ary, 0)
If IsError(res) Then
MsgBox "You must change value of " & checkcell
Exit Sub
End If
Next i
End Sub

please let me know what you think, and if anyone else has improvements
on this please feel free to post them, it's always good to improve

somethinglikeant






monir wrote:
Hello;

I would like to check the values in 3 cells, each against a separate range
of values, before proceeding with the computation. (And not using Data
Validation Lists).

Suppose the 3 cells to be checked, in the order presented, a
... C9, C10, and C11
and their validation ranges a
... A1:A5, B1:B6, and C1:C7 respectively.

I would appreciate your help to include in the VBA code something like:
IF Range("C9").Value < 'any value in the Range("A1:A5") Then
MsgBox "You must Change C9 value"
' don't exit this IF until you fix the value in C9
Enf IF
IF Range("C10").Value < 'any value in Range("B1:B6") Then
MsgBox "You must Change C10 value"
' don't exit this IF until you fix the value in C10
Enf IF
IF Range("C11").Value < 'any value in Range("C1:C7") Then
MsgBox "You must Change C11 value"
' don't exit this IF until you fix the value in C11
Enf IF
'continue with the program

Thank you.

Orderly Validation Against Separate Ranges
 

Hello;

Here's just a thought! ... Could I remove Exit Sub, and place your code in:

Do Until Err.Number = 0
.........................................
' your modified code
...........................................
Loop

so that the custom error message would continue popping up until the
referenced value(s) is fixed, as applicable.

Thank you.
"Dave Peterson" wrote:

One way:

Dim myMsg as string
mymsg = ""
with worksheets("sheet9999")
If application.countif(.range("a1:a5"),.range("C9").v alue) = 0 then
mymsg = "C9"
end if
If application.countif(.range("b1:b6"),.range("C10"). value) = 0 then
mymsg = mymsg & " " & "C10"
end if
If application.countif(.range("c1:c7"),.range("C11"). value) = 0 then
mymsg = mymsg & " " & "C11"
end if
if mymsg = "" then
'keep going
else
msgbox "Please fix these cells:" & vblf & trim(mymsg)
exit sub
end if

monir wrote:

Hello;

I would like to check the values in 3 cells, each against a separate range
of values, before proceeding with the computation. (And not using Data
Validation Lists).

Suppose the 3 cells to be checked, in the order presented, a
... C9, C10, and C11
and their validation ranges a
... A1:A5, B1:B6, and C1:C7 respectively.

I would appreciate your help to include in the VBA code something like:
IF Range("C9").Value < 'any value in the Range("A1:A5") Then
MsgBox "You must Change C9 value"
' don't exit this IF until you fix the value in C9
Enf IF
IF Range("C10").Value < 'any value in Range("B1:B6") Then
MsgBox "You must Change C10 value"
' don't exit this IF until you fix the value in C10
Enf IF
IF Range("C11").Value < 'any value in Range("C1:C7") Then
MsgBox "You must Change C11 value"
' don't exit this IF until you fix the value in C11
Enf IF
'continue with the program

Thank you.

--

Dave Peterson

Orderly Validation Against Separate Ranges
 

The code I wrote will stop the macro and let the user adjust the values. Then
the macro has to be restarted.

You could add an inputbox that prompts for each value (when the initial value is
wrong). Then validates that entry and keeps checking.

But if you're going to do that, why not just create a small userform with 3
comboboxes on it that force the user to choose from one of the valid values?

If you want to try...

I built a small userform with 3 comboboxes and 2 commandbuttons (ok/cancel) on
it.

This is the code that goes behind the userform:

Option Explicit
Private Sub ComboBox1_Change()
CheckAllComboboxes
End Sub
Private Sub ComboBox2_Change()
CheckAllComboboxes
End Sub
Private Sub ComboBox3_Change()
CheckAllComboboxes
End Sub
Private Sub CommandButton1_Click()
'ok button

With Worksheets("sheet9999")
.Range("c9").Value = Me.ComboBox1.Value
.Range("c10").Value = Me.ComboBox2.Value
.Range("c11").Value = Me.ComboBox3.Value
End With

Unload Me

End Sub
Private Sub CommandButton2_Click()
'cancel button
Unload Me
End Sub
Private Sub UserForm_Initialize()

Dim ValRngA As Range
Dim ValRngB As Range
Dim ValRngC As Range

With Worksheets("sheet9999")
Set ValRngA = .Range("a1:A5")
Set ValRngB = .Range("b1:B6")
Set ValRngC = .Range("c1:c7")
End With

With Me.ComboBox1
.List = ValRngA.Value
.Style = fmStyleDropDownList
End With

With Me.ComboBox2
.List = ValRngB.Value
.Style = fmStyleDropDownList
End With

With Me.ComboBox3
.List = ValRngC.Value
.Style = fmStyleDropDownList
End With

With Me.CommandButton1
.Caption = "Ok"
.Enabled = False
End With
Me.CommandButton2.Caption = "Cancel"
End Sub
Sub CheckAllComboboxes()
Dim Ctrl As Control
Dim AreAllChosen As Boolean

AreAllChosen = True
For Each Ctrl In Me.Controls
If TypeOf Ctrl Is MSForms.ComboBox Then
If Ctrl.Object.ListIndex < 0 Then
AreAllChosen = False
Exit For
End If
End If
Next Ctrl

Me.CommandButton1.Enabled = AreAllChosen

End Sub

And to show the userform you can put this in a general module:

Option Explicit
sub ShowMyForm()
userform1.show
end sub

You can plop a button from the Forms toolbar onto the worksheet and assign that
macro to the button or you can incorporate it into your code someother way.

You may want to review these two pages from Debra Dalgleish's site:
http://www.contextures.com/xlUserForm01.html
and
http://www.contextures.com/xlUserForm02.html


monir wrote:

Hello;

Here's just a thought! ... Could I remove Exit Sub, and place your code in:

Do Until Err.Number = 0
.........................................
' your modified code
..........................................
Loop

so that the custom error message would continue popping up until the
referenced value(s) is fixed, as applicable.

Thank you.
"Dave Peterson" wrote:

One way:

Dim myMsg as string
mymsg = ""
with worksheets("sheet9999")
If application.countif(.range("a1:a5"),.range("C9").v alue) = 0 then
mymsg = "C9"
end if
If application.countif(.range("b1:b6"),.range("C10"). value) = 0 then
mymsg = mymsg & " " & "C10"
end if
If application.countif(.range("c1:c7"),.range("C11"). value) = 0 then
mymsg = mymsg & " " & "C11"
end if
if mymsg = "" then
'keep going
else
msgbox "Please fix these cells:" & vblf & trim(mymsg)
exit sub
end if

monir wrote:

Hello;

I would like to check the values in 3 cells, each against a separate range
of values, before proceeding with the computation. (And not using Data
Validation Lists).

Suppose the 3 cells to be checked, in the order presented, a
... C9, C10, and C11
and their validation ranges a
... A1:A5, B1:B6, and C1:C7 respectively.

I would appreciate your help to include in the VBA code something like:
IF Range("C9").Value < 'any value in the Range("A1:A5") Then
MsgBox "You must Change C9 value"
' don't exit this IF until you fix the value in C9
Enf IF
IF Range("C10").Value < 'any value in Range("B1:B6") Then
MsgBox "You must Change C10 value"
' don't exit this IF until you fix the value in C10
Enf IF
IF Range("C11").Value < 'any value in Range("C1:C7") Then
MsgBox "You must Change C11 value"
' don't exit this IF until you fix the value in C11
Enf IF
'continue with the program

Thank you.

--

Dave Peterson


--

Dave Peterson

Orderly Validation Against Separate Ranges
 

Dave;

Thank you very much for your UserForm/ComboBox code. It's another demo of
your excellent understanding of how this stuff works!
I've tested your code separately, and it works fine. There're, however,
couple of difficulties.

Back to my original post. The values in C9, C10, C11 must be CHECKED and
VALIDATED, separately and in the same order. That's to say, first
check/validate C9, then C10, and finally C11, as applicable.

For example. Suppose the value in C9 does not match any of the discrete
values in its respective range A1:A5, and the values in C10 & C11 do match.
The UserForm should then show ComboBox1 enabled, and both ComboBox2 &
ComboBox3 disabled. Select the value of C9 from the dropdown list in
ComboBox1, and ComboBox1 becomes disabled. Now, if you re-show the UserForm,
non of the ComboBoxes should be enabbled.

Another example. Suppose C10 does not match, but C9 and C11 do. The
UserForm should in this case show ComboBox2 enabled, and ComboBox1 &
ComboBox3 disabled. Select the value for C10 from its ComboBox2 list, and
ComboBox2 becomes disablesd.

Another example. Suppose the values in C10 & C11 do not match, and C9 does.
The UserForm should then show ComboBox1 disabled, ComboBox2 enabled, and
ComboBox3 disabled. Select the value for C10 from ComboBox2 list, and
ComboBox2 becomes disabled and CombBox3 enabled. Fix the value in C11, and
its ComboBox3 becomes disabled.

Another possibility. If the values in C9, C10, and C11 do not match, the
UserForm should show only ComboBox1 enabled. Fix the C9 value, and ComboBox1
becomes disabled, ComboBox2 becomes enabled, and ComboBox3 remains disabled
until the value in C10 is selected. And so on ...

There're 8 possibilities, and it might be difficult to code all of them
intellegentally in your approach, and still have the code simple enough to be
integrated into the main procedure. One may have to include w/s events which
could complicate the integration even further!!

The somethinglikeant's approach appears to be easily adaptable to the
situation with minimum adjustments. Will post the (working) procedure
shortly for your (and others) review.

Thank you.


"Dave Peterson" wrote:

The code I wrote will stop the macro and let the user adjust the values. Then
the macro has to be restarted.

You could add an inputbox that prompts for each value (when the initial value is
wrong). Then validates that entry and keeps checking.

But if you're going to do that, why not just create a small userform with 3
comboboxes on it that force the user to choose from one of the valid values?

If you want to try...

I built a small userform with 3 comboboxes and 2 commandbuttons (ok/cancel) on
it.

This is the code that goes behind the userform:

Option Explicit
Private Sub ComboBox1_Change()
CheckAllComboboxes
End Sub
Private Sub ComboBox2_Change()
CheckAllComboboxes
End Sub
Private Sub ComboBox3_Change()
CheckAllComboboxes
End Sub
Private Sub CommandButton1_Click()
'ok button

With Worksheets("sheet9999")
.Range("c9").Value = Me.ComboBox1.Value
.Range("c10").Value = Me.ComboBox2.Value
.Range("c11").Value = Me.ComboBox3.Value
End With

Unload Me

End Sub
Private Sub CommandButton2_Click()
'cancel button
Unload Me
End Sub
Private Sub UserForm_Initialize()

Dim ValRngA As Range
Dim ValRngB As Range
Dim ValRngC As Range

With Worksheets("sheet9999")
Set ValRngA = .Range("a1:A5")
Set ValRngB = .Range("b1:B6")
Set ValRngC = .Range("c1:c7")
End With

With Me.ComboBox1
.List = ValRngA.Value
.Style = fmStyleDropDownList
End With

With Me.ComboBox2
.List = ValRngB.Value
.Style = fmStyleDropDownList
End With

With Me.ComboBox3
.List = ValRngC.Value
.Style = fmStyleDropDownList
End With

With Me.CommandButton1
.Caption = "Ok"
.Enabled = False
End With
Me.CommandButton2.Caption = "Cancel"
End Sub
Sub CheckAllComboboxes()
Dim Ctrl As Control
Dim AreAllChosen As Boolean

AreAllChosen = True
For Each Ctrl In Me.Controls
If TypeOf Ctrl Is MSForms.ComboBox Then
If Ctrl.Object.ListIndex < 0 Then
AreAllChosen = False
Exit For
End If
End If
Next Ctrl

Me.CommandButton1.Enabled = AreAllChosen

End Sub

And to show the userform you can put this in a general module:

Option Explicit
sub ShowMyForm()
userform1.show
end sub

You can plop a button from the Forms toolbar onto the worksheet and assign that
macro to the button or you can incorporate it into your code someother way.

You may want to review these two pages from Debra Dalgleish's site:
http://www.contextures.com/xlUserForm01.html
and
http://www.contextures.com/xlUserForm02.html


monir wrote:

Hello;

Here's just a thought! ... Could I remove Exit Sub, and place your code in:

Do Until Err.Number = 0
.........................................
' your modified code
..........................................
Loop

so that the custom error message would continue popping up until the
referenced value(s) is fixed, as applicable.

Thank you.
"Dave Peterson" wrote:

One way:

Dim myMsg as string
mymsg = ""
with worksheets("sheet9999")
If application.countif(.range("a1:a5"),.range("C9").v alue) = 0 then
mymsg = "C9"
end if
If application.countif(.range("b1:b6"),.range("C10"). value) = 0 then
mymsg = mymsg & " " & "C10"
end if
If application.countif(.range("c1:c7"),.range("C11"). value) = 0 then
mymsg = mymsg & " " & "C11"
end if
if mymsg = "" then
'keep going
else
msgbox "Please fix these cells:" & vblf & trim(mymsg)
exit sub
end if

monir wrote:

Hello;

I would like to check the values in 3 cells, each against a separate range
of values, before proceeding with the computation. (And not using Data
Validation Lists).

Suppose the 3 cells to be checked, in the order presented, a
... C9, C10, and C11
and their validation ranges a
... A1:A5, B1:B6, and C1:C7 respectively.

I would appreciate your help to include in the VBA code something like:
IF Range("C9").Value < 'any value in the Range("A1:A5") Then
MsgBox "You must Change C9 value"
' don't exit this IF until you fix the value in C9
Enf IF
IF Range("C10").Value < 'any value in Range("B1:B6") Then
MsgBox "You must Change C10 value"
' don't exit this IF until you fix the value in C10
Enf IF
IF Range("C11").Value < 'any value in Range("C1:C7") Then
MsgBox "You must Change C11 value"
' don't exit this IF until you fix the value in C11
Enf IF
'continue with the program

Thank you.

--

Dave Peterson


--

Dave Peterson

Orderly Validation Against Separate Ranges
 

somethinglikeant;

I've modified your code slightly, and it works fine now, though there's
always room for improvement!
The procedure :(macro Sub CheckValues() runs from a button):
1. checks the values stored in cells C9, C10 and C11, regardless of which
cell is selected among the three
2. validates the 3 cells, in the same order, each against discrete values
stored in A1:A5, B1:B6 and C1:C7 respectively.
3. If the value in C9, C10, or C11 does not match its respective validation
values, a message is displayed, and the cell is highlighted
4. When this macro is called from Sub myMainProgram () as shown below, the
program is terminated as a result of using End instead of Exit Sub in Sub
CheckValues () to suspend the execution of myCode2 that follows the statement
that called the sub procedure

Sub myMainProgram ()
' ... myCode1
CheckValues
' ... myCode2
End Sub

Sub CheckVales ()
For I = 1 To 3
If I = 1 Then
ary = Range("A1:A5"): checkcell = [C9] 'for EAR
End If
If I = 2 Then
ary = Range("B1:B6"): checkcell = [C10] 'for (P/D)
End If
If I = 3 Then
ary = Range("C1:C7"): checkcell = [C11] 'for theta s
End If
res = Application.Match(checkcell, ary, 0)
If IsError(res) Then
MsgBox "Please change The Input value of " & checkcell
Select Case I
Case 1
Range("C9").Select
Case 2
Range("C10").Select
Case 3
Range("C11").Select
End Select
End
End If
Next I
End Sub

Thank you all for your help. Regards.

"monir" wrote:

Dave;

Thank you very much for your UserForm/ComboBox code. It's another demo of
your excellent understanding of how this stuff works!
I've tested your code separately, and it works fine. There're, however,
couple of difficulties.

Back to my original post. The values in C9, C10, C11 must be CHECKED and
VALIDATED, separately and in the same order. That's to say, first
check/validate C9, then C10, and finally C11, as applicable.

For example. Suppose the value in C9 does not match any of the discrete
values in its respective range A1:A5, and the values in C10 & C11 do match.
The UserForm should then show ComboBox1 enabled, and both ComboBox2 &
ComboBox3 disabled. Select the value of C9 from the dropdown list in
ComboBox1, and ComboBox1 becomes disabled. Now, if you re-show the UserForm,
non of the ComboBoxes should be enabbled.

Another example. Suppose C10 does not match, but C9 and C11 do. The
UserForm should in this case show ComboBox2 enabled, and ComboBox1 &
ComboBox3 disabled. Select the value for C10 from its ComboBox2 list, and
ComboBox2 becomes disablesd.

Another example. Suppose the values in C10 & C11 do not match, and C9 does.
The UserForm should then show ComboBox1 disabled, ComboBox2 enabled, and
ComboBox3 disabled. Select the value for C10 from ComboBox2 list, and
ComboBox2 becomes disabled and CombBox3 enabled. Fix the value in C11, and
its ComboBox3 becomes disabled.

Another possibility. If the values in C9, C10, and C11 do not match, the
UserForm should show only ComboBox1 enabled. Fix the C9 value, and ComboBox1
becomes disabled, ComboBox2 becomes enabled, and ComboBox3 remains disabled
until the value in C10 is selected. And so on ...

There're 8 possibilities, and it might be difficult to code all of them
intellegentally in your approach, and still have the code simple enough to be
integrated into the main procedure. One may have to include w/s events which
could complicate the integration even further!!

The somethinglikeant's approach appears to be easily adaptable to the
situation with minimum adjustments. Will post the (working) procedure
shortly for your (and others) review.

Thank you.


"Dave Peterson" wrote:

The code I wrote will stop the macro and let the user adjust the values. Then
the macro has to be restarted.

You could add an inputbox that prompts for each value (when the initial value is
wrong). Then validates that entry and keeps checking.

But if you're going to do that, why not just create a small userform with 3
comboboxes on it that force the user to choose from one of the valid values?

If you want to try...

I built a small userform with 3 comboboxes and 2 commandbuttons (ok/cancel) on
it.

This is the code that goes behind the userform:

Option Explicit
Private Sub ComboBox1_Change()
CheckAllComboboxes
End Sub
Private Sub ComboBox2_Change()
CheckAllComboboxes
End Sub
Private Sub ComboBox3_Change()
CheckAllComboboxes
End Sub
Private Sub CommandButton1_Click()
'ok button

With Worksheets("sheet9999")
.Range("c9").Value = Me.ComboBox1.Value
.Range("c10").Value = Me.ComboBox2.Value
.Range("c11").Value = Me.ComboBox3.Value
End With

Unload Me

End Sub
Private Sub CommandButton2_Click()
'cancel button
Unload Me
End Sub
Private Sub UserForm_Initialize()

Dim ValRngA As Range
Dim ValRngB As Range
Dim ValRngC As Range

With Worksheets("sheet9999")
Set ValRngA = .Range("a1:A5")
Set ValRngB = .Range("b1:B6")
Set ValRngC = .Range("c1:c7")
End With

With Me.ComboBox1
.List = ValRngA.Value
.Style = fmStyleDropDownList
End With

With Me.ComboBox2
.List = ValRngB.Value
.Style = fmStyleDropDownList
End With

With Me.ComboBox3
.List = ValRngC.Value
.Style = fmStyleDropDownList
End With

With Me.CommandButton1
.Caption = "Ok"
.Enabled = False
End With
Me.CommandButton2.Caption = "Cancel"
End Sub
Sub CheckAllComboboxes()
Dim Ctrl As Control
Dim AreAllChosen As Boolean

AreAllChosen = True
For Each Ctrl In Me.Controls
If TypeOf Ctrl Is MSForms.ComboBox Then
If Ctrl.Object.ListIndex < 0 Then
AreAllChosen = False
Exit For
End If
End If
Next Ctrl

Me.CommandButton1.Enabled = AreAllChosen

End Sub

And to show the userform you can put this in a general module:

Option Explicit
sub ShowMyForm()
userform1.show
end sub

You can plop a button from the Forms toolbar onto the worksheet and assign that
macro to the button or you can incorporate it into your code someother way.

You may want to review these two pages from Debra Dalgleish's site:
http://www.contextures.com/xlUserForm01.html
and
http://www.contextures.com/xlUserForm02.html


monir wrote:

Hello;

Here's just a thought! ... Could I remove Exit Sub, and place your code in:

Do Until Err.Number = 0
.........................................
' your modified code
..........................................
Loop

so that the custom error message would continue popping up until the
referenced value(s) is fixed, as applicable.

Thank you.
"Dave Peterson" wrote:

One way:

Dim myMsg as string
mymsg = ""
with worksheets("sheet9999")
If application.countif(.range("a1:a5"),.range("C9").v alue) = 0 then
mymsg = "C9"
end if
If application.countif(.range("b1:b6"),.range("C10"). value) = 0 then
mymsg = mymsg & " " & "C10"
end if
If application.countif(.range("c1:c7"),.range("C11"). value) = 0 then
mymsg = mymsg & " " & "C11"
end if
if mymsg = "" then
'keep going
else
msgbox "Please fix these cells:" & vblf & trim(mymsg)
exit sub
end if

monir wrote:

Hello;

I would like to check the values in 3 cells, each against a separate range
of values, before proceeding with the computation. (And not using Data
Validation Lists).

Suppose the 3 cells to be checked, in the order presented, a
... C9, C10, and C11
and their validation ranges a
... A1:A5, B1:B6, and C1:C7 respectively.

I would appreciate your help to include in the VBA code something like:
IF Range("C9").Value < 'any value in the Range("A1:A5") Then
MsgBox "You must Change C9 value"
' don't exit this IF until you fix the value in C9
Enf IF
IF Range("C10").Value < 'any value in Range("B1:B6") Then
MsgBox "You must Change C10 value"
' don't exit this IF until you fix the value in C10
Enf IF
IF Range("C11").Value < 'any value in Range("C1:C7") Then
MsgBox "You must Change C11 value"
' don't exit this IF until you fix the value in C11
Enf IF
'continue with the program

Thank you.

--

Dave Peterson


--

Dave Peterson

Orderly Validation Against Separate Ranges
 

Reply was made to your thread on MrExcell....
one way

Code
-------------------

Dim MyCell, MyRng, i As Integer, flg As Boolean

MyCell = Array("C9","C10","C11")
MyRng = Array("A1:A5","B1:B6","C1:C7")

For i = 0 To UBound(MyCell)
If Application.CountIf(Range(MyRng(i)),Range(MyCell(i )).VAlue) = 0 Then
flg = True
Exit For
End If
Next
If flg Then
MsgBox "Check the value in cell " & MyCell(i)
End I
-------------------

--
jindo
-----------------------------------------------------------------------
jindon's Profile: http://www.excelforum.com/member.php...fo&userid=1313
View this thread: http://www.excelforum.com/showthread.php?threadid=55070