UserForm Questtion
 

Greetings,
I use the following code to get input from the user. ListBox1 provides a
list of "Areas" (NAreaName) in my project. ListBox2 provides a list of
potential new "Areas" to add. TextBox1 provides a way for the user to enter
a new "Area" by typing its name. The length is checked and also a check is
done to see if the newly entered name is already in the project. This all
works fine.

Here is the question; One of my users happened to click on ListBox2 and then
entered a new "Area" in TextBox1 which is really what was desired, however,
when the macro was finished, the "Area" selected in ListBox2 was added to
the project vs the new name. How would you change this code to deal with
this mis entry issue?

Hopefully I have explained the issue so it can be understood.

Thanks in advance for your help!

Ray

Do
UserForm2.Show
NAreaName = UserForm2.TextBox1.Value

If Not UserForm2.ListBox2.ListIndex = -1 Then ' selecting typical
NAreaName = UserForm2.ListBox2.Value
End If
If Not bResponse Then
MsgBox "Cancel Selected"
End
End If
If NAreaName = "" Then
blnExit = False
ElseIf Len(NAreaName) 22 Then ' check if name fits length
MsgBox "Name is more than 22 characters! Try Again"
blnExit = False
ElseIf Application.CountIf(Range("AreaNames"), _
NAreaName) 0 Then ' see if name has been used
MsgBox "Name has already been used."
blnExit = False
Else
blnExit = True
End If
Loop While blnExit = False

UserForm Questtion
 

Add an AfterUpdate Event Procedure to Textbox1 and
ListBox2 that "overrides" whatever is already entered in
the other; e.g:

Sub TextBox1_AfterUpdate()
UserForm1.ListBox2.ListIndex = -1
End Sub

Sub ListBox2_AfterUpdate()
UserForm1.TextBox1.Value = ""
End Sub

For more sophistication you could even add a messagebox to
verify what the user wants to do (e.g. override the
original selection (OK) or keep the original selection
(Cancel)).

K Dales

-----Original Message-----
Greetings,
I use the following code to get input from the user.
ListBox1 provides a
list of "Areas" (NAreaName) in my project. ListBox2
provides a list of
potential new "Areas" to add. TextBox1 provides a way for
the user to enter
a new "Area" by typing its name. The length is checked
and also a check is
done to see if the newly entered name is already in the
project. This all
works fine.

Here is the question; One of my users happened to click
on ListBox2 and then
entered a new "Area" in TextBox1 which is really what was
desired, however,
when the macro was finished, the "Area" selected in
ListBox2 was added to
the project vs the new name. How would you change this
code to deal with
this mis entry issue?

Hopefully I have explained the issue so it can be
understood.

Thanks in advance for your help!

Ray

Do
UserForm2.Show
NAreaName = UserForm2.TextBox1.Value

If Not UserForm2.ListBox2.ListIndex = -1 Then '
selecting typical
NAreaName = UserForm2.ListBox2.Value
End If
If Not bResponse Then
MsgBox "Cancel Selected"
End
End If
If NAreaName = "" Then
blnExit = False
ElseIf Len(NAreaName) 22 Then ' check if
name fits length
MsgBox "Name is more than 22 characters!
Try Again"
blnExit = False
ElseIf Application.CountIf(Range
("AreaNames"), _
NAreaName) 0 Then ' see if name has
been used
MsgBox "Name has already been used."
blnExit = False
Else
blnExit = True
End If
Loop While blnExit = False


.

UserForm Questtion
 

Hi,

Thanks for your reply. I am confused as to where you would put the calls to
these subs in my code. Can you point me in the right direction?

Thanks again!

Ray
K Dales wrote in message
...
Add an AfterUpdate Event Procedure to Textbox1 and
ListBox2 that "overrides" whatever is already entered in
the other; e.g:

Sub TextBox1_AfterUpdate()
UserForm1.ListBox2.ListIndex = -1
End Sub

Sub ListBox2_AfterUpdate()
UserForm1.TextBox1.Value = ""
End Sub

For more sophistication you could even add a messagebox to
verify what the user wants to do (e.g. override the
original selection (OK) or keep the original selection
(Cancel)).

K Dales

-----Original Message-----
Greetings,
I use the following code to get input from the user.
ListBox1 provides a
list of "Areas" (NAreaName) in my project. ListBox2
provides a list of
potential new "Areas" to add. TextBox1 provides a way for
the user to enter
a new "Area" by typing its name. The length is checked
and also a check is
done to see if the newly entered name is already in the
project. This all
works fine.

Here is the question; One of my users happened to click
on ListBox2 and then
entered a new "Area" in TextBox1 which is really what was
desired, however,
when the macro was finished, the "Area" selected in
ListBox2 was added to
the project vs the new name. How would you change this
code to deal with
this mis entry issue?

Hopefully I have explained the issue so it can be
understood.

Thanks in advance for your help!

Ray

Do
UserForm2.Show
NAreaName = UserForm2.TextBox1.Value

If Not UserForm2.ListBox2.ListIndex = -1 Then '
selecting typical
NAreaName = UserForm2.ListBox2.Value
End If
If Not bResponse Then
MsgBox "Cancel Selected"
End
End If
If NAreaName = "" Then
blnExit = False
ElseIf Len(NAreaName) 22 Then ' check if
name fits length
MsgBox "Name is more than 22 characters!
Try Again"
blnExit = False
ElseIf Application.CountIf(Range
("AreaNames"), _
NAreaName) 0 Then ' see if name has
been used
MsgBox "Name has already been used."
blnExit = False
Else
blnExit = True
End If
Loop While blnExit = False


.

UserForm Questtion
 

Hello Ray;
You do not need to call these from your code: they are
separate event procedures for the controls on your form.
Are you familiar with Event Procedures? If not, I suggest
looking up the topic in VBA help. Basically, they are
subs that run automatically when certain events take place
in your application.

From the VBA editor when you are looking at your Userform
design, if you right-click on the Listbox or Textbox you
will get the option "View Code" (i.e. the code associated
with that control). If you select this, you will see a
code pane for your UserForm in the VBA editor and by
default it will create a sub called ListBox2_Change() or
TextBox1_Change(). Erase that; you want the AfterUpdate
Event. If you call the sub ListBox2_AfterUpdate() VBA
knows to run it after you change the value in ListBox2.

So, type the code I supplied in that code pane for your
UserForm and it should run whenever you change one of
those two controls.

Hope this all makes sense!

K Dales

-----Original Message-----
Hi,

Thanks for your reply. I am confused as to where you
would put the calls to
these subs in my code. Can you point me in the right
direction?

Thanks again!

Ray
K Dales wrote in
message
...
Add an AfterUpdate Event Procedure to Textbox1 and
ListBox2 that "overrides" whatever is already entered in
the other; e.g:

Sub TextBox1_AfterUpdate()
UserForm1.ListBox2.ListIndex = -1
End Sub

Sub ListBox2_AfterUpdate()
UserForm1.TextBox1.Value = ""
End Sub

For more sophistication you could even add a messagebox
to
verify what the user wants to do (e.g. override the
original selection (OK) or keep the original selection
(Cancel)).

K Dales

-----Original Message-----
Greetings,
I use the following code to get input from the user.
ListBox1 provides a
list of "Areas" (NAreaName) in my project. ListBox2
provides a list of
potential new "Areas" to add. TextBox1 provides a way
for
the user to enter
a new "Area" by typing its name. The length is checked
and also a check is
done to see if the newly entered name is already in the
project. This all
works fine.

Here is the question; One of my users happened to click
on ListBox2 and then
entered a new "Area" in TextBox1 which is really what
was
desired, however,
when the macro was finished, the "Area" selected in
ListBox2 was added to
the project vs the new name. How would you change this
code to deal with
this mis entry issue?

Hopefully I have explained the issue so it can be
understood.

Thanks in advance for your help!

Ray

Do
UserForm2.Show
NAreaName = UserForm2.TextBox1.Value

If Not UserForm2.ListBox2.ListIndex = -1 Then '
selecting typical
NAreaName = UserForm2.ListBox2.Value
End If
If Not bResponse Then
MsgBox "Cancel Selected"
End
End If
If NAreaName = "" Then
blnExit = False
ElseIf Len(NAreaName) 22 Then ' check if
name fits length
MsgBox "Name is more than 22
characters!
Try Again"
blnExit = False
ElseIf Application.CountIf(Range
("AreaNames"), _
NAreaName) 0 Then ' see if name
has
been used
MsgBox "Name has already been used."
blnExit = False
Else
blnExit = True
End If
Loop While blnExit = False


.



.

UserForm Questtion
 

Thanks, I had never run into event Procedures. I am sure that there are more
things for me to learn. Your code works beautifully!

K Dales wrote in message
...
Hello Ray;
You do not need to call these from your code: they are
separate event procedures for the controls on your form.
Are you familiar with Event Procedures? If not, I suggest
looking up the topic in VBA help. Basically, they are
subs that run automatically when certain events take place
in your application.

From the VBA editor when you are looking at your Userform
design, if you right-click on the Listbox or Textbox you
will get the option "View Code" (i.e. the code associated
with that control). If you select this, you will see a
code pane for your UserForm in the VBA editor and by
default it will create a sub called ListBox2_Change() or
TextBox1_Change(). Erase that; you want the AfterUpdate
Event. If you call the sub ListBox2_AfterUpdate() VBA
knows to run it after you change the value in ListBox2.

So, type the code I supplied in that code pane for your
UserForm and it should run whenever you change one of
those two controls.

Hope this all makes sense!

K Dales

-----Original Message-----
Hi,

Thanks for your reply. I am confused as to where you
would put the calls to
these subs in my code. Can you point me in the right
direction?

Thanks again!

Ray
K Dales wrote in
message
...
Add an AfterUpdate Event Procedure to Textbox1 and
ListBox2 that "overrides" whatever is already entered in
the other; e.g:

Sub TextBox1_AfterUpdate()
UserForm1.ListBox2.ListIndex = -1
End Sub

Sub ListBox2_AfterUpdate()
UserForm1.TextBox1.Value = ""
End Sub

For more sophistication you could even add a messagebox
to
verify what the user wants to do (e.g. override the
original selection (OK) or keep the original selection
(Cancel)).

K Dales

-----Original Message-----
Greetings,
I use the following code to get input from the user.
ListBox1 provides a
list of "Areas" (NAreaName) in my project. ListBox2
provides a list of
potential new "Areas" to add. TextBox1 provides a way
for
the user to enter
a new "Area" by typing its name. The length is checked
and also a check is
done to see if the newly entered name is already in the
project. This all
works fine.

Here is the question; One of my users happened to click
on ListBox2 and then
entered a new "Area" in TextBox1 which is really what
was
desired, however,
when the macro was finished, the "Area" selected in
ListBox2 was added to
the project vs the new name. How would you change this
code to deal with
this mis entry issue?

Hopefully I have explained the issue so it can be
understood.

Thanks in advance for your help!

Ray

Do
UserForm2.Show
NAreaName = UserForm2.TextBox1.Value

If Not UserForm2.ListBox2.ListIndex = -1 Then '
selecting typical
NAreaName = UserForm2.ListBox2.Value
End If
If Not bResponse Then
MsgBox "Cancel Selected"
End
End If
If NAreaName = "" Then
blnExit = False
ElseIf Len(NAreaName) 22 Then ' check if
name fits length
MsgBox "Name is more than 22
characters!
Try Again"
blnExit = False
ElseIf Application.CountIf(Range
("AreaNames"), _
NAreaName) 0 Then ' see if name
has
been used
MsgBox "Name has already been used."
blnExit = False
Else
blnExit = True
End If
Loop While blnExit = False


.



.