If...Then and Loops

[Gary Thomson]

Hi,

I have the following code:

Dim School As String
School = InputBox("Which School do you wish to see the
budget for?", "Choose School")
If (School <> "Bonnyrigg") Then
MsgBox ("You have entered an incorrect school name")
School = InputBox("Which School do you wish to see the
budget for?", "Choose School")
End If

The code then has a series of Case statements which
execute depending on the value assigned to School.

I have a couple of problems with this:

(1) I wan the If statment to read if(or
(school<>"bonnyrigg", school<>"Bryans",
school<>"Cuiken", ... ,school<>"Woodburn") then

where bonnyrigg, bryans, cuiken, .... , woodburn are in
the range B7:B42 in a worksheet called "School Rolls".

i.e. so that if the school name entered is not one of the
above schools, the user will be informed of this and will
have to re-enter the school.

(2) If the user enters an incorrect school name on
his/her first attempt, the msgbox is displayed, and the
user is then asked to re-enter a valid school name.
However, if the user again enters an incorrect name, the
macro just stops running (i.e. it does not again inform
the user that the school name is invlaid, and ask the user
to re-enter a valid school name). I would like some sort
of loop which will continually ask the user to enter a
valid school name until the user does so.

Many Thanks,

Gary.

 

[Kalle]

with 72 entries in your shoollist, you should consider using a listfeld to
display the names so users can easily select the desired shool.

lstbox.rowsource= "YourShoolsSheet!A1:A72"

 

[Nikos Yannacopoulos]

Gary,

Try this:

Dim rng As Range
Dim School As String

Set rng = Range("'School Rolls'!B7:B42")
msg = "Which School do you wish to see the budget for?"
ttl = "Choose School"

start_check:
School = InputBox(msg, ttl)
If School = "" Then Exit Sub
For Each Cell In rng
If Cell.Value = School Then
'code to do what you want
Exit Sub
End If
Next
MsgBox ("You have entered an incorrect school name")
GoTo start_check

Note: having the user type in a school name is not very user friendly!
Alternatives you might want to consider:

1. Since you already have the school names on a sheet: ask the user to
select one (the cell) in the sheet, and then get its name in code by:
School = ActiveCell.Value
and continue with your code

2. Even better (the one I prefer):
Make a form in the VB window, and place a listbox contol on it (assume they
are called UserForm1 and ListBox1 respectively). Double-click on the list
form to be taken to the associated code window, and paste this code:

School = ListBox1
UserForm1.Hide
'code to do what you want

Then return to your original macro and use the following code instead of the
previous one:

Dim rng As Range
Dim School As String

Set rng = Range("'School Rolls'!B7:B42")
Load UserForm1
For Each cell In rng
UserForm1.ListBox1.AddItem cell.Value
Next
UserForm1.Show

Now once the macro is run the user sees a form with the school names (which
may well be stored in a hidden sheet!), and all they have to do is
double-click on their choice. A much more elegant approach with a
"professiona" look.

HTH,
Nikos

 

[Bob Phillips]

Hi Gary,

Here's some code for you

Dim School As String
Dim oFound As Range

Do
School = InputBox("Which School do you wish to see the budget for?",
"Choose School")
On Error Resume Next
Set oFound = Range("B7:B42").Find(School)
If oFound Is Nothing Then
MsgBox ("You have entered an incorrect school name")
End If
Loop Until Not oFound Is Nothing

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Gary Thomson]

Hi Nikos,

I have to say that your "best" method is fantastic. looks
very professional, as you stated.

Could you perhaps do one final thing for me?

I am quite new to VBA code, although I have a basic
understanding of them and how the macros run, etc.

Could you possibly explain what ech line of your code
does? (I've included what I entered below)

(in the listbox code):

School = ListBox1
UserForm1.Hide
'code to do what you want

(in the macro code)

Dim rng As Range
Dim School As String

Set rng = Range("'School Rolls'!B7:B42")
Load UserForm1
For Each cell In rng
UserForm1.ListBox1.AddItem cell.Value
Next
UserForm1.Show


Many, Many Thanks,

Gary.

 

[Bob Phillips]

Gary,

If you are going to use a form, you do not need to add every item to the
listbox as shown, just point the listbox at the data on the worksheet, like
so

Load UserForm1
UserForm1.ListBox1.RowSource = "'School Rolls'!B7:B42"
UserForm1.Show

This is quicker, and neater. It would even be better to reset the range in
the Userform_Activate event

Private Sub UserForm_Activate()
UserForm1.ListBox1.RowSource = "'School Rolls'!B7:B42"
End Sub

and then all you need in your macro is the

Userform1.Show

command.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)