Custom Userforms
 

I want to create a userform that shows when a particular cell is selected on
a worksheet. Then I need a textbox also on the userform that will only allow
50
characters to be entered, and I would like to be able to show the user how
many characters they have used while they are typing in the text box, like
something that shows 0/50 and as they type it would change 16/34. Is there a
way these actions can be done?

Custom Userforms
 

Use the change event of the textbox to update a label and limit the number
of characters. It fires on each key entry.

--
Regards,
Tom Ogilvy


"jnf40" wrote in message
...
I want to create a userform that shows when a particular cell is selected
on
a worksheet. Then I need a textbox also on the userform that will only
allow
50
characters to be entered, and I would like to be able to show the user how
many characters they have used while they are typing in the text box, like
something that shows 0/50 and as they type it would change 16/34. Is there
a
way these actions can be done?

Custom Userforms
 

Thanks, that will help with the 50 characters, now is there a way to get the
userform to automatically open when a particular cell on a worksheet is
selected? For arguments sake let's say Range("A1").

"Tom Ogilvy" wrote:

Use the change event of the textbox to update a label and limit the number
of characters. It fires on each key entry.

--
Regards,
Tom Ogilvy


"jnf40" wrote in message
...
I want to create a userform that shows when a particular cell is selected
on
a worksheet. Then I need a textbox also on the userform that will only
allow
50
characters to be entered, and I would like to be able to show the user how
many characters they have used while they are typing in the text box, like
something that shows 0/50 and as they type it would change 16/34. Is there
a
way these actions can be done?

Custom Userforms
 

Use the selection change event.

Right click on the sheet tab and select view code.

In the left dropdown at the top of the module select Worksheet
in the right dropdown at the top of the module select SelectionChange

this will place the declaration in the module.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub

you can put your code he

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
if Target.Address = "$A$1" then
Userform1.Show
End if
End Sub

--
Regards,
Tom Ogilvy



"jnf40" wrote in message
...
Thanks, that will help with the 50 characters, now is there a way to get
the
userform to automatically open when a particular cell on a worksheet is
selected? For arguments sake let's say Range("A1").

"Tom Ogilvy" wrote:

Use the change event of the textbox to update a label and limit the
number
of characters. It fires on each key entry.

--
Regards,
Tom Ogilvy


"jnf40" wrote in message
...
I want to create a userform that shows when a particular cell is
selected
on
a worksheet. Then I need a textbox also on the userform that will only
allow
50
characters to be entered, and I would like to be able to show the user
how
many characters they have used while they are typing in the text box,
like
something that shows 0/50 and as they type it would change 16/34. Is
there
a
way these actions can be done?

Custom Userforms
 

Thanks. Can you also step me through the updating the label. I know how to
set it up to limit the number of characters allowed in the textbox.

"Tom Ogilvy" wrote:

Use the selection change event.

Right click on the sheet tab and select view code.

In the left dropdown at the top of the module select Worksheet
in the right dropdown at the top of the module select SelectionChange

this will place the declaration in the module.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub

you can put your code he

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
if Target.Address = "$A$1" then
Userform1.Show
End if
End Sub

--
Regards,
Tom Ogilvy



"jnf40" wrote in message
...
Thanks, that will help with the 50 characters, now is there a way to get
the
userform to automatically open when a particular cell on a worksheet is
selected? For arguments sake let's say Range("A1").

"Tom Ogilvy" wrote:

Use the change event of the textbox to update a label and limit the
number
of characters. It fires on each key entry.

--
Regards,
Tom Ogilvy


"jnf40" wrote in message
...
I want to create a userform that shows when a particular cell is
selected
on
a worksheet. Then I need a textbox also on the userform that will only
allow
50
characters to be entered, and I would like to be able to show the user
how
many characters they have used while they are typing in the text box,
like
something that shows 0/50 and as they type it would change 16/34. Is
there
a
way these actions can be done?

Custom Userforms
 

Public bBlockEvents as Boolean


Private Sub Textbox1_Change()
Dim cnt as Long
if bBlockEvents then exit sub
cnt = len(Textbox1.Value)
if cnt 50 then
bBlockevents = True
me.Testbox1.Value = Left(Textbox1.Value,50)
me. label1 = "50/50"
bBlockEvents = False
else
me.label1 = cnt & "/50"
end if
End Sub

--
Regards,
Tom Ogilvy


"jnf40" wrote in message
...
Thanks. Can you also step me through the updating the label. I know how to
set it up to limit the number of characters allowed in the textbox.

"Tom Ogilvy" wrote:

Use the selection change event.

Right click on the sheet tab and select view code.

In the left dropdown at the top of the module select Worksheet
in the right dropdown at the top of the module select SelectionChange

this will place the declaration in the module.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub

you can put your code he

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
if Target.Address = "$A$1" then
Userform1.Show
End if
End Sub

--
Regards,
Tom Ogilvy



"jnf40" wrote in message
...
Thanks, that will help with the 50 characters, now is there a way to
get
the
userform to automatically open when a particular cell on a worksheet
is
selected? For arguments sake let's say Range("A1").

"Tom Ogilvy" wrote:

Use the change event of the textbox to update a label and limit the
number
of characters. It fires on each key entry.

--
Regards,
Tom Ogilvy


"jnf40" wrote in message
...
I want to create a userform that shows when a particular cell is
selected
on
a worksheet. Then I need a textbox also on the userform that will
only
allow
50
characters to be entered, and I would like to be able to show the
user
how
many characters they have used while they are typing in the text
box,
like
something that shows 0/50 and as they type it would change 16/34.
Is
there
a
way these actions can be done?

Custom Userforms
 

Thank you so much for your help. I am still having a problem getting the form
to show when I select the cell though. I have the several cells merged
together and I have given the range a unique name, but it won't seem to show
the form when I select the range.

"Tom Ogilvy" wrote:

Public bBlockEvents as Boolean


Private Sub Textbox1_Change()
Dim cnt as Long
if bBlockEvents then exit sub
cnt = len(Textbox1.Value)
if cnt 50 then
bBlockevents = True
me.Testbox1.Value = Left(Textbox1.Value,50)
me. label1 = "50/50"
bBlockEvents = False
else
me.label1 = cnt & "/50"
end if
End Sub

--
Regards,
Tom Ogilvy


"jnf40" wrote in message
...
Thanks. Can you also step me through the updating the label. I know how to
set it up to limit the number of characters allowed in the textbox.

"Tom Ogilvy" wrote:

Use the selection change event.

Right click on the sheet tab and select view code.

In the left dropdown at the top of the module select Worksheet
in the right dropdown at the top of the module select SelectionChange

this will place the declaration in the module.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub

you can put your code he

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
if Target.Address = "$A$1" then
Userform1.Show
End if
End Sub

--
Regards,
Tom Ogilvy



"jnf40" wrote in message
...
Thanks, that will help with the 50 characters, now is there a way to
get
the
userform to automatically open when a particular cell on a worksheet
is
selected? For arguments sake let's say Range("A1").

"Tom Ogilvy" wrote:

Use the change event of the textbox to update a label and limit the
number
of characters. It fires on each key entry.

--
Regards,
Tom Ogilvy


"jnf40" wrote in message
...
I want to create a userform that shows when a particular cell is
selected
on
a worksheet. Then I need a textbox also on the userform that will
only
allow
50
characters to be entered, and I would like to be able to show the
user
how
many characters they have used while they are typing in the text
box,
like
something that shows 0/50 and as they type it would change 16/34.
Is
there
a
way these actions can be done?

Custom Userforms
 

I got the userform to load. Now the countdown procedure you gave me for the
textbox to update the label works great, i even added another label that is
updated with the countdown. Is there a way that when the userform opens and
there is already text in the textbox for the label to be updated to show that
length of text and be able to continue from that point on.

As it is right now if I choose the target address and the userform loads as
I type in the textbox one label updates as to how many characters I've used
and the other label updates as to how many characters I have left to use.
When I close the userform and then open it again it shows what has been
entered but the labels go back to 0 and 50, when I start typing it picks up
where it should in the count, which works okay, I was just wondering if it
could show the actual counts instead of reverting back to 0 and 50, I think
that would look better to users.
Thanks again for all your help

"jnf40" wrote:

Thank you so much for your help. I am still having a problem getting the form
to show when I select the cell though. I have the several cells merged
together and I have given the range a unique name, but it won't seem to show
the form when I select the range.

"Tom Ogilvy" wrote:

Public bBlockEvents as Boolean


Private Sub Textbox1_Change()
Dim cnt as Long
if bBlockEvents then exit sub
cnt = len(Textbox1.Value)
if cnt 50 then
bBlockevents = True
me.Testbox1.Value = Left(Textbox1.Value,50)
me. label1 = "50/50"
bBlockEvents = False
else
me.label1 = cnt & "/50"
end if
End Sub

--
Regards,
Tom Ogilvy


"jnf40" wrote in message
...
Thanks. Can you also step me through the updating the label. I know how to
set it up to limit the number of characters allowed in the textbox.

"Tom Ogilvy" wrote:

Use the selection change event.

Right click on the sheet tab and select view code.

In the left dropdown at the top of the module select Worksheet
in the right dropdown at the top of the module select SelectionChange

this will place the declaration in the module.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub

you can put your code he

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
if Target.Address = "$A$1" then
Userform1.Show
End if
End Sub

--
Regards,
Tom Ogilvy



"jnf40" wrote in message
...
Thanks, that will help with the 50 characters, now is there a way to
get
the
userform to automatically open when a particular cell on a worksheet
is
selected? For arguments sake let's say Range("A1").

"Tom Ogilvy" wrote:

Use the change event of the textbox to update a label and limit the
number
of characters. It fires on each key entry.

--
Regards,
Tom Ogilvy


"jnf40" wrote in message
...
I want to create a userform that shows when a particular cell is
selected
on
a worksheet. Then I need a textbox also on the userform that will
only
allow
50
characters to be entered, and I would like to be able to show the
user
how
many characters they have used while they are typing in the text
box,
like
something that shows 0/50 and as they type it would change 16/34.
Is
there
a
way these actions can be done?

Custom Userforms
 

Run similar code in the

Private Sub Userform_Initialize()

event or the
Private Sub Userform_Activate()

event.

Note the Userform is NOT replaced by the name of the userform in the
declaration. It is the generic Userform. Select it from the dropdowns at
the top of the module to be sure.
--
Regards,
Tom Ogilvy


"jnf40" wrote in message
...
I got the userform to load. Now the countdown procedure you gave me for
the
textbox to update the label works great, i even added another label that
is
updated with the countdown. Is there a way that when the userform opens
and
there is already text in the textbox for the label to be updated to show
that
length of text and be able to continue from that point on.

As it is right now if I choose the target address and the userform loads
as
I type in the textbox one label updates as to how many characters I've
used
and the other label updates as to how many characters I have left to use.
When I close the userform and then open it again it shows what has been
entered but the labels go back to 0 and 50, when I start typing it picks
up
where it should in the count, which works okay, I was just wondering if it
could show the actual counts instead of reverting back to 0 and 50, I
think
that would look better to users.
Thanks again for all your help

"jnf40" wrote:

Thank you so much for your help. I am still having a problem getting the
form
to show when I select the cell though. I have the several cells merged
together and I have given the range a unique name, but it won't seem to
show
the form when I select the range.

"Tom Ogilvy" wrote:

Public bBlockEvents as Boolean


Private Sub Textbox1_Change()
Dim cnt as Long
if bBlockEvents then exit sub
cnt = len(Textbox1.Value)
if cnt 50 then
bBlockevents = True
me.Testbox1.Value = Left(Textbox1.Value,50)
me. label1 = "50/50"
bBlockEvents = False
else
me.label1 = cnt & "/50"
end if
End Sub

--
Regards,
Tom Ogilvy


"jnf40" wrote in message
...
Thanks. Can you also step me through the updating the label. I know
how to
set it up to limit the number of characters allowed in the textbox.

"Tom Ogilvy" wrote:

Use the selection change event.

Right click on the sheet tab and select view code.

In the left dropdown at the top of the module select Worksheet
in the right dropdown at the top of the module select
SelectionChange

this will place the declaration in the module.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub

you can put your code he

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
if Target.Address = "$A$1" then
Userform1.Show
End if
End Sub

--
Regards,
Tom Ogilvy



"jnf40" wrote in message
...
Thanks, that will help with the 50 characters, now is there a
way to
get
the
userform to automatically open when a particular cell on a
worksheet
is
selected? For arguments sake let's say Range("A1").

"Tom Ogilvy" wrote:

Use the change event of the textbox to update a label and
limit the
number
of characters. It fires on each key entry.

--
Regards,
Tom Ogilvy


"jnf40" wrote in message
...
I want to create a userform that shows when a particular
cell is
selected
on
a worksheet. Then I need a textbox also on the userform that
will
only
allow
50
characters to be entered, and I would like to be able to
show the
user
how
many characters they have used while they are typing in the
text
box,
like
something that shows 0/50 and as they type it would change
16/34.
Is
there
a
way these actions can be done?

Custom Userforms
 

Tom this fixed it! Thank you very much for your assistance and quick
responses to my questions.

"Tom Ogilvy" wrote:

Run similar code in the

Private Sub Userform_Initialize()

event or the
Private Sub Userform_Activate()

event.

Note the Userform is NOT replaced by the name of the userform in the
declaration. It is the generic Userform. Select it from the dropdowns at
the top of the module to be sure.
--
Regards,
Tom Ogilvy


"jnf40" wrote in message
...
I got the userform to load. Now the countdown procedure you gave me for
the
textbox to update the label works great, i even added another label that
is
updated with the countdown. Is there a way that when the userform opens
and
there is already text in the textbox for the label to be updated to show
that
length of text and be able to continue from that point on.

As it is right now if I choose the target address and the userform loads
as
I type in the textbox one label updates as to how many characters I've
used
and the other label updates as to how many characters I have left to use.
When I close the userform and then open it again it shows what has been
entered but the labels go back to 0 and 50, when I start typing it picks
up
where it should in the count, which works okay, I was just wondering if it
could show the actual counts instead of reverting back to 0 and 50, I
think
that would look better to users.
Thanks again for all your help

"jnf40" wrote:

Thank you so much for your help. I am still having a problem getting the
form
to show when I select the cell though. I have the several cells merged
together and I have given the range a unique name, but it won't seem to
show
the form when I select the range.

"Tom Ogilvy" wrote:

Public bBlockEvents as Boolean


Private Sub Textbox1_Change()
Dim cnt as Long
if bBlockEvents then exit sub
cnt = len(Textbox1.Value)
if cnt 50 then
bBlockevents = True
me.Testbox1.Value = Left(Textbox1.Value,50)
me. label1 = "50/50"
bBlockEvents = False
else
me.label1 = cnt & "/50"
end if
End Sub

--
Regards,
Tom Ogilvy


"jnf40" wrote in message
...
Thanks. Can you also step me through the updating the label. I know
how to
set it up to limit the number of characters allowed in the textbox.

"Tom Ogilvy" wrote:

Use the selection change event.

Right click on the sheet tab and select view code.

In the left dropdown at the top of the module select Worksheet
in the right dropdown at the top of the module select
SelectionChange

this will place the declaration in the module.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub

you can put your code he

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
if Target.Address = "$A$1" then
Userform1.Show
End if
End Sub

--
Regards,
Tom Ogilvy



"jnf40" wrote in message
...
Thanks, that will help with the 50 characters, now is there a
way to
get
the
userform to automatically open when a particular cell on a
worksheet
is
selected? For arguments sake let's say Range("A1").

"Tom Ogilvy" wrote:

Use the change event of the textbox to update a label and
limit the
number
of characters. It fires on each key entry.

--
Regards,
Tom Ogilvy


"jnf40" wrote in message
...
I want to create a userform that shows when a particular
cell is
selected
on
a worksheet. Then I need a textbox also on the userform that
will
only
allow
50
characters to be entered, and I would like to be able to
show the
user
how
many characters they have used while they are typing in the
text
box,
like
something that shows 0/50 and as they type it would change
16/34.
Is
there
a
way these actions can be done?

Custom Userforms
 

Tom,

Just out of curiousity, what is the safeguard in including a Boolean
variable. Your code works well without it.

Public bBlockEvents as Boolean

Private Sub Textbox1_Change()
Dim cnt as Long
if bBlockEvents then exit sub
cnt = len(Textbox1.Value)
if cnt 50 then
bBlockevents = True
me.Testbox1.Value = Left(Textbox1.Value,50)
me. label1 = "50/50"
bBlockEvents = False
else
me.label1 = cnt & "/50"
end if
End Sub

davidm


--
davidm
------------------------------------------------------------------------
davidm's Profile: http://www.excelforum.com/member.php...o&userid=20645
View this thread: http://www.excelforum.com/showthread...hreadid=512756

Custom Userforms
 

Tom,
Another question about opening the userform with text in the textbox. When I
do this the cursor is at the end of what has been typed, is there a way to
have the existing text highlighted so that when the user starts typing it
replaces the existing text with the new?

"jnf40" wrote:

Tom this fixed it! Thank you very much for your assistance and quick
responses to my questions.

"Tom Ogilvy" wrote:

Run similar code in the

Private Sub Userform_Initialize()

event or the
Private Sub Userform_Activate()

event.

Note the Userform is NOT replaced by the name of the userform in the
declaration. It is the generic Userform. Select it from the dropdowns at
the top of the module to be sure.
--
Regards,
Tom Ogilvy


"jnf40" wrote in message
...
I got the userform to load. Now the countdown procedure you gave me for
the
textbox to update the label works great, i even added another label that
is
updated with the countdown. Is there a way that when the userform opens
and
there is already text in the textbox for the label to be updated to show
that
length of text and be able to continue from that point on.

As it is right now if I choose the target address and the userform loads
as
I type in the textbox one label updates as to how many characters I've
used
and the other label updates as to how many characters I have left to use.
When I close the userform and then open it again it shows what has been
entered but the labels go back to 0 and 50, when I start typing it picks
up
where it should in the count, which works okay, I was just wondering if it
could show the actual counts instead of reverting back to 0 and 50, I
think
that would look better to users.
Thanks again for all your help

"jnf40" wrote:

Thank you so much for your help. I am still having a problem getting the
form
to show when I select the cell though. I have the several cells merged
together and I have given the range a unique name, but it won't seem to
show
the form when I select the range.

"Tom Ogilvy" wrote:

Public bBlockEvents as Boolean


Private Sub Textbox1_Change()
Dim cnt as Long
if bBlockEvents then exit sub
cnt = len(Textbox1.Value)
if cnt 50 then
bBlockevents = True
me.Testbox1.Value = Left(Textbox1.Value,50)
me. label1 = "50/50"
bBlockEvents = False
else
me.label1 = cnt & "/50"
end if
End Sub

--
Regards,
Tom Ogilvy


"jnf40" wrote in message
...
Thanks. Can you also step me through the updating the label. I know
how to
set it up to limit the number of characters allowed in the textbox.

"Tom Ogilvy" wrote:

Use the selection change event.

Right click on the sheet tab and select view code.

In the left dropdown at the top of the module select Worksheet
in the right dropdown at the top of the module select
SelectionChange

this will place the declaration in the module.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub

you can put your code he

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
if Target.Address = "$A$1" then
Userform1.Show
End if
End Sub

--
Regards,
Tom Ogilvy



"jnf40" wrote in message
...
Thanks, that will help with the 50 characters, now is there a
way to
get
the
userform to automatically open when a particular cell on a
worksheet
is
selected? For arguments sake let's say Range("A1").

"Tom Ogilvy" wrote:

Use the change event of the textbox to update a label and
limit the
number
of characters. It fires on each key entry.

--
Regards,
Tom Ogilvy


"jnf40" wrote in message
...
I want to create a userform that shows when a particular
cell is
selected
on
a worksheet. Then I need a textbox also on the userform that
will
only
allow
50
characters to be entered, and I would like to be able to
show the
user
how
many characters they have used while they are typing in the
text
box,
like
something that shows 0/50 and as they type it would change
16/34.
Is
there
a
way these actions can be done?

Custom Userforms
 

Tom,
I figured out the existing text being highlighted issue, once again thanks
for all your help.

"jnf40" wrote:

Tom,
Another question about opening the userform with text in the textbox. When I
do this the cursor is at the end of what has been typed, is there a way to
have the existing text highlighted so that when the user starts typing it
replaces the existing text with the new?

"jnf40" wrote:

Tom this fixed it! Thank you very much for your assistance and quick
responses to my questions.

"Tom Ogilvy" wrote:

Run similar code in the

Private Sub Userform_Initialize()

event or the
Private Sub Userform_Activate()

event.

Note the Userform is NOT replaced by the name of the userform in the
declaration. It is the generic Userform. Select it from the dropdowns at
the top of the module to be sure.
--
Regards,
Tom Ogilvy


"jnf40" wrote in message
...
I got the userform to load. Now the countdown procedure you gave me for
the
textbox to update the label works great, i even added another label that
is
updated with the countdown. Is there a way that when the userform opens
and
there is already text in the textbox for the label to be updated to show
that
length of text and be able to continue from that point on.

As it is right now if I choose the target address and the userform loads
as
I type in the textbox one label updates as to how many characters I've
used
and the other label updates as to how many characters I have left to use.
When I close the userform and then open it again it shows what has been
entered but the labels go back to 0 and 50, when I start typing it picks
up
where it should in the count, which works okay, I was just wondering if it
could show the actual counts instead of reverting back to 0 and 50, I
think
that would look better to users.
Thanks again for all your help

"jnf40" wrote:

Thank you so much for your help. I am still having a problem getting the
form
to show when I select the cell though. I have the several cells merged
together and I have given the range a unique name, but it won't seem to
show
the form when I select the range.

"Tom Ogilvy" wrote:

Public bBlockEvents as Boolean


Private Sub Textbox1_Change()
Dim cnt as Long
if bBlockEvents then exit sub
cnt = len(Textbox1.Value)
if cnt 50 then
bBlockevents = True
me.Testbox1.Value = Left(Textbox1.Value,50)
me. label1 = "50/50"
bBlockEvents = False
else
me.label1 = cnt & "/50"
end if
End Sub

--
Regards,
Tom Ogilvy


"jnf40" wrote in message
...
Thanks. Can you also step me through the updating the label. I know
how to
set it up to limit the number of characters allowed in the textbox.

"Tom Ogilvy" wrote:

Use the selection change event.

Right click on the sheet tab and select view code.

In the left dropdown at the top of the module select Worksheet
in the right dropdown at the top of the module select
SelectionChange

this will place the declaration in the module.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub

you can put your code he

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
if Target.Address = "$A$1" then
Userform1.Show
End if
End Sub

--
Regards,
Tom Ogilvy



"jnf40" wrote in message
...
Thanks, that will help with the 50 characters, now is there a
way to
get
the
userform to automatically open when a particular cell on a
worksheet
is
selected? For arguments sake let's say Range("A1").

"Tom Ogilvy" wrote:

Use the change event of the textbox to update a label and
limit the
number
of characters. It fires on each key entry.

--
Regards,
Tom Ogilvy


"jnf40" wrote in message
...
I want to create a userform that shows when a particular
cell is
selected
on
a worksheet. Then I need a textbox also on the userform that
will
only
allow
50
characters to be entered, and I would like to be able to
show the
user
how
many characters they have used while they are typing in the
text
box,
like
something that shows 0/50 and as they type it would change
16/34.
Is
there
a
way these actions can be done?