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?
> > > > >
> > > >
> > > >
> > > >
> >
> >
> >