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?

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" <jnf40@discussions.microsoft.com> wrote in message
news:68F2A826-0ADD-4327-8ED1-5A9C4E7035CC@microsoft.com...
> 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?
>

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" <jnf40@discussions.microsoft.com> wrote in message
> news:68F2A826-0ADD-4327-8ED1-5A9C4E7035CC@microsoft.com...
> > 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?
> >
>
>
>

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 here:

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

--
Regards,
Tom Ogilvy



"jnf40" <jnf40@discussions.microsoft.com> wrote in message
news:2DCCBC9B-4A79-4E71-9174-AD659E7B3225@microsoft.com...
> 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" <jnf40@discussions.microsoft.com> wrote in message
> > news:68F2A826-0ADD-4327-8ED1-5A9C4E7035CC@microsoft.com...
> > > 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?
> > >
> >
> >
> >

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 here:
>
> Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> if Target.Address = "$A$1" then
> Userform1.Show
> End if
> End Sub
>
> --
> Regards,
> Tom Ogilvy
>
>
>
> "jnf40" <jnf40@discussions.microsoft.com> wrote in message
> news:2DCCBC9B-4A79-4E71-9174-AD659E7B3225@microsoft.com...
> > 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" <jnf40@discussions.microsoft.com> wrote in message
> > > news:68F2A826-0ADD-4327-8ED1-5A9C4E7035CC@microsoft.com...
> > > > 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?
> > > >
> > >
> > >
> > >
>
>
>

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" <jnf40@discussions.microsoft.com> wrote in message
news:6701781A-BDD2-4485-BEDF-0AFA47F71CE8@microsoft.com...
> 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 here:
> >
> > Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> > if Target.Address = "$A$1" then
> > Userform1.Show
> > End if
> > End Sub
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> >
> > "jnf40" <jnf40@discussions.microsoft.com> wrote in message
> > news:2DCCBC9B-4A79-4E71-9174-AD659E7B3225@microsoft.com...
> > > 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" <jnf40@discussions.microsoft.com> wrote in message
> > > > news:68F2A826-0ADD-4327-8ED1-5A9C4E7035CC@microsoft.com...
> > > > > 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?
> > > > >
> > > >
> > > >
> > > >
> >
> >
> >

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" <jnf40@discussions.microsoft.com> wrote in message
> news:6701781A-BDD2-4485-BEDF-0AFA47F71CE8@microsoft.com...
> > 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 here:
> > >
> > > Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> > > if Target.Address = "$A$1" then
> > > Userform1.Show
> > > End if
> > > End Sub
> > >
> > > --
> > > Regards,
> > > Tom Ogilvy
> > >
> > >
> > >
> > > "jnf40" <jnf40@discussions.microsoft.com> wrote in message
> > > news:2DCCBC9B-4A79-4E71-9174-AD659E7B3225@microsoft.com...
> > > > 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" <jnf40@discussions.microsoft.com> wrote in message
> > > > > news:68F2A826-0ADD-4327-8ED1-5A9C4E7035CC@microsoft.com...
> > > > > > 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?
> > > > > >
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>
>

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" <jnf40@discussions.microsoft.com> wrote in message
> > news:6701781A-BDD2-4485-BEDF-0AFA47F71CE8@microsoft.com...
> > > 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 here:
> > > >
> > > > Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> > > > if Target.Address = "$A$1" then
> > > > Userform1.Show
> > > > End if
> > > > End Sub
> > > >
> > > > --
> > > > Regards,
> > > > Tom Ogilvy
> > > >
> > > >
> > > >
> > > > "jnf40" <jnf40@discussions.microsoft.com> wrote in message
> > > > news:2DCCBC9B-4A79-4E71-9174-AD659E7B3225@microsoft.com...
> > > > > 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" <jnf40@discussions.microsoft.com> wrote in message
> > > > > > news:68F2A826-0ADD-4327-8ED1-5A9C4E7035CC@microsoft.com...
> > > > > > > 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?
> > > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
> >
> >
> >

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" <jnf40@discussions.microsoft.com> wrote in message
news:4BEFECEE-6592-4194-A7BA-E566DE5BA56F@microsoft.com...
> 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" <jnf40@discussions.microsoft.com> wrote in message
> > > news:6701781A-BDD2-4485-BEDF-0AFA47F71CE8@microsoft.com...
> > > > 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 here:
> > > > >
> > > > > Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> > > > > if Target.Address = "$A$1" then
> > > > > Userform1.Show
> > > > > End if
> > > > > End Sub
> > > > >
> > > > > --
> > > > > Regards,
> > > > > Tom Ogilvy
> > > > >
> > > > >
> > > > >
> > > > > "jnf40" <jnf40@discussions.microsoft.com> wrote in message
> > > > > news:2DCCBC9B-4A79-4E71-9174-AD659E7B3225@microsoft.com...
> > > > > > 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" <jnf40@discussions.microsoft.com> wrote in message
> > > > > > > news:68F2A826-0ADD-4327-8ED1-5A9C4E7035CC@microsoft.com...
> > > > > > > > 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?
> > > > > > > >
> > > > > > >
> > > > > > >
> > > > > > >
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >

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" <jnf40@discussions.microsoft.com> wrote in message
> news:4BEFECEE-6592-4194-A7BA-E566DE5BA56F@microsoft.com...
> > 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" <jnf40@discussions.microsoft.com> wrote in message
> > > > news:6701781A-BDD2-4485-BEDF-0AFA47F71CE8@microsoft.com...
> > > > > 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 here:
> > > > > >
> > > > > > Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> > > > > > if Target.Address = "$A$1" then
> > > > > > Userform1.Show
> > > > > > End if
> > > > > > End Sub
> > > > > >
> > > > > > --
> > > > > > Regards,
> > > > > > Tom Ogilvy
> > > > > >
> > > > > >
> > > > > >
> > > > > > "jnf40" <jnf40@discussions.microsoft.com> wrote in message
> > > > > > news:2DCCBC9B-4A79-4E71-9174-AD659E7B3225@microsoft.com...
> > > > > > > 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" <jnf40@discussions.microsoft.com> wrote in message
> > > > > > > > news:68F2A826-0ADD-4327-8ED1-5A9C4E7035CC@microsoft.com...
> > > > > > > > > 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?
> > > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
>
>
>

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

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" <jnf40@discussions.microsoft.com> wrote in message
> > news:4BEFECEE-6592-4194-A7BA-E566DE5BA56F@microsoft.com...
> > > 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" <jnf40@discussions.microsoft.com> wrote in message
> > > > > news:6701781A-BDD2-4485-BEDF-0AFA47F71CE8@microsoft.com...
> > > > > > 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 here:
> > > > > > >
> > > > > > > Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> > > > > > > if Target.Address = "$A$1" then
> > > > > > > Userform1.Show
> > > > > > > End if
> > > > > > > End Sub
> > > > > > >
> > > > > > > --
> > > > > > > Regards,
> > > > > > > Tom Ogilvy
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > > "jnf40" <jnf40@discussions.microsoft.com> wrote in message
> > > > > > > news:2DCCBC9B-4A79-4E71-9174-AD659E7B3225@microsoft.com...
> > > > > > > > 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" <jnf40@discussions.microsoft.com> wrote in message
> > > > > > > > > news:68F2A826-0ADD-4327-8ED1-5A9C4E7035CC@microsoft.com...
> > > > > > > > > > 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?
> > > > > > > > > >
> > > > > > > > >
> > > > > > > > >
> > > > > > > > >
> > > > > > >
> > > > > > >
> > > > > > >
> > > > >
> > > > >
> > > > >
> >
> >
> >

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" <jnf40@discussions.microsoft.com> wrote in message
> > > news:4BEFECEE-6592-4194-A7BA-E566DE5BA56F@microsoft.com...
> > > > 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" <jnf40@discussions.microsoft.com> wrote in message
> > > > > > news:6701781A-BDD2-4485-BEDF-0AFA47F71CE8@microsoft.com...
> > > > > > > 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 here:
> > > > > > > >
> > > > > > > > Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> > > > > > > > if Target.Address = "$A$1" then
> > > > > > > > Userform1.Show
> > > > > > > > End if
> > > > > > > > End Sub
> > > > > > > >
> > > > > > > > --
> > > > > > > > Regards,
> > > > > > > > Tom Ogilvy
> > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > > > "jnf40" <jnf40@discussions.microsoft.com> wrote in message
> > > > > > > > news:2DCCBC9B-4A79-4E71-9174-AD659E7B3225@microsoft.com...
> > > > > > > > > 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" <jnf40@discussions.microsoft.com> wrote in message
> > > > > > > > > > news:68F2A826-0ADD-4327-8ED1-5A9C4E7035CC@microsoft.com...
> > > > > > > > > > > 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?
> > > > > > > > > > >
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > >
> > > > > >
> > > > > >
> > >
> > >
> > >