Excel 97 - VB question

I have a form with some textboxes on it (textbox1 and textbox2). When the
user tabs from textbox1 to the textbox2, I validate the contents of the
textbox1 in the textbox1_AfterUpdate event handler. If the content textbox1
is invalid, I want to keep the cursor in textbox1, rather than tabbing to
textbox2.

I have tried using textbox1.SetFocus method in the textbox1_AfterUpdate and
textbox2_Enter event handlers. Neither worked.

What is a good way to do this? Thanks.

you can use the textbox's Exit event...

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox1.Text = "X" Then
Cancel = True
End If
End Sub

"Old Car" wrote:

> I have a form with some textboxes on it (textbox1 and textbox2). When the
> user tabs from textbox1 to the textbox2, I validate the contents of the
> textbox1 in the textbox1_AfterUpdate event handler. If the content textbox1
> is invalid, I want to keep the cursor in textbox1, rather than tabbing to
> textbox2.
>
> I have tried using textbox1.SetFocus method in the textbox1_AfterUpdate and
> textbox2_Enter event handlers. Neither worked.
>
> What is a good way to do this? Thanks.
>
>
>

Thanks. Using that technique, I find that closing the window causes that
event to fire. Is there a way to test for the fact that the Exit event is
firing because a "Close" button is being selected?

"Patrick Molloy" <[email protected]> wrote in message
news:[email protected]...
> you can use the textbox's Exit event...
>
> Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
> If TextBox1.Text = "X" Then
> Cancel = True
> End If
> End Sub
>
> "Old Car" wrote:
>
> > I have a form with some textboxes on it (textbox1 and textbox2). When
the
> > user tabs from textbox1 to the textbox2, I validate the contents of the
> > textbox1 in the textbox1_AfterUpdate event handler. If the content
textbox1
> > is invalid, I want to keep the cursor in textbox1, rather than tabbing
to
> > textbox2.
> >
> > I have tried using textbox1.SetFocus method in the textbox1_AfterUpdate
and
> > textbox2_Enter event handlers. Neither worked.
> >
> > What is a good way to do this? Thanks.
> >
> >
> >

Here is a technique that Rob Bovey posted some time ago. It is a kludge as
Rob says, but it may work for you

There's no direct way to do this. I created a workaround for one project
where I needed to do this, but it was very clunky. As a rule, I always hide
the first row and column of worksheets in my projects. This gives me scratch
space to work in and is required for this solution to work (at least the
hidden first column is required in this case).


In my hidden first column I placed the number 1 in every cell of the
area the user might interact with (let's say A1:A100). Then in cell A101 I
placed the formula


=COUNTBLANK(A1:A100)


and gave it the range name "CheckInsert". In cell A102 I placed the *array
formula* (entered with Ctrl+Shift+Enter)


=MATCH(TRUE,ISBLANK(A1:A100),0*)


and gave it the range name "FindInsert".


Each time the worksheet_calculate event fired, I would check the
CheckInsert cell. If it contained any number other than zero, I would know
the user had inserted a row, and how many they had inserted. The FindInsert
range would then contain the number of the row where the insert began.


I would then turn off calculation, take the action I needed to take, add
1s into the just-inserted rows, and turn calculation back on. You can extend
this general method to include finding deletions by using sequentially
numbered cells in the first column, rather than 1s.


--
Rob Bovey, MCSE
The Payne Consulting Group
http://www.payneconsulting.com



--

HTH

RP
(remove nothere from the email address if mailing direct)


"Old Car" <[email protected]> wrote in message
news:2Smbe.6014$Nc.5775@trnddc05...
> Thanks. Using that technique, I find that closing the window causes that
> event to fire. Is there a way to test for the fact that the Exit event is
> firing because a "Close" button is being selected?

Are you using a msgbox to tell the user that the textbox is invalid? If yes,
maybe you could move that message to a label right near the textbox.

If the user closes the userform, the label gets updated, but it's pretty quick
and probably not noticeable. But the good thing is that it won't disturb the
user with another msgbox.

Old Car wrote:
>
> Thanks. Using that technique, I find that closing the window causes that
> event to fire. Is there a way to test for the fact that the Exit event is
> firing because a "Close" button is being selected?
>
> "Patrick Molloy" <[email protected]> wrote in message
> news:[email protected]...
> > you can use the textbox's Exit event...
> >
> > Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
> > If TextBox1.Text = "X" Then
> > Cancel = True
> > End If
> > End Sub
> >
> > "Old Car" wrote:
> >
> > > I have a form with some textboxes on it (textbox1 and textbox2). When
> the
> > > user tabs from textbox1 to the textbox2, I validate the contents of the
> > > textbox1 in the textbox1_AfterUpdate event handler. If the content
> textbox1
> > > is invalid, I want to keep the cursor in textbox1, rather than tabbing
> to
> > > textbox2.
> > >
> > > I have tried using textbox1.SetFocus method in the textbox1_AfterUpdate
> and
> > > textbox2_Enter event handlers. Neither worked.
> > >
> > > What is a good way to do this? Thanks.
> > >
> > >
> > >

--

Dave Peterson

Thank you for your suggestion. Yes, I use a message box to tell the user
the content of textbox1 is invalid. I would prefer using a messsage box
rather than a label. Is there a way I can detect within textbox1_Exit that
the window is being closed, and therefore the message box should not be
displayed?

"Dave Peterson" <[email protected]> wrote in message
news:[email protected]...
> Are you using a msgbox to tell the user that the textbox is invalid? If
yes,
> maybe you could move that message to a label right near the textbox.
>
> If the user closes the userform, the label gets updated, but it's pretty
quick
> and probably not noticeable. But the good thing is that it won't disturb
the
> user with another msgbox.
>
> Old Car wrote:
> >
> > Thanks. Using that technique, I find that closing the window causes
that
> > event to fire. Is there a way to test for the fact that the Exit event
is
> > firing because a "Close" button is being selected?
> >
> > "Patrick Molloy" <[email protected]> wrote in
message
> > news:[email protected]...
> > > you can use the textbox's Exit event...
> > >
> > > Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
> > > If TextBox1.Text = "X" Then
> > > Cancel = True
> > > End If
> > > End Sub
> > >
> > > "Old Car" wrote:
> > >
> > > > I have a form with some textboxes on it (textbox1 and textbox2).
When
> > the
> > > > user tabs from textbox1 to the textbox2, I validate the contents of
the
> > > > textbox1 in the textbox1_AfterUpdate event handler. If the content
> > textbox1
> > > > is invalid, I want to keep the cursor in textbox1, rather than
tabbing
> > to
> > > > textbox2.
> > > >
> > > > I have tried using textbox1.SetFocus method in the
textbox1_AfterUpdate
> > and
> > > > textbox2_Enter event handlers. Neither worked.
> > > >
> > > > What is a good way to do this? Thanks.
> > > >
> > > >
> > > >
>
> --
>
> Dave Peterson

Sorry, answering another question. I have found it now, so will go and post
there :-)

Bob

"Bob Phillips" <[email protected]> wrote in message
news:[email protected]...
> Here is a technique that Rob Bovey posted some time ago. It is a kludge as
> Rob says, but it may work for you
>
> There's no direct way to do this. I created a workaround for one
project
> where I needed to do this, but it was very clunky. As a rule, I always
hide
> the first row and column of worksheets in my projects. This gives me
scratch
> space to work in and is required for this solution to work (at least the
> hidden first column is required in this case).
>
>
> In my hidden first column I placed the number 1 in every cell of the
> area the user might interact with (let's say A1:A100). Then in cell A101 I
> placed the formula
>
>
> =COUNTBLANK(A1:A100)
>
>
> and gave it the range name "CheckInsert". In cell A102 I placed the *array
> formula* (entered with Ctrl+Shift+Enter)
>
>
> =MATCH(TRUE,ISBLANK(A1:A100),0*)
>
>
> and gave it the range name "FindInsert".
>
>
> Each time the worksheet_calculate event fired, I would check the
> CheckInsert cell. If it contained any number other than zero, I would know
> the user had inserted a row, and how many they had inserted. The
FindInsert
> range would then contain the number of the row where the insert began.
>
>
> I would then turn off calculation, take the action I needed to take,
add
> 1s into the just-inserted rows, and turn calculation back on. You can
extend
> this general method to include finding deletions by using sequentially
> numbered cells in the first column, rather than 1s.
>
>
> --
> Rob Bovey, MCSE
> The Payne Consulting Group
> http://www.payneconsulting.com
>
>
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Old Car" <[email protected]> wrote in message
> news:2Smbe.6014$Nc.5775@trnddc05...
> > Thanks. Using that technique, I find that closing the window causes
that
> > event to fire. Is there a way to test for the fact that the Exit event
is
> > firing because a "Close" button is being selected?
>
>

This might work...

Option Explicit
Dim BlkProc As Boolean
Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If BlkProc = True Then Exit Sub
If IsNumeric(Me.TextBox1.Value) Then
MsgBox "nope"
Cancel = True
End If
End Sub
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
BlkProc = True
End If
End Sub


Old Car wrote:
>
> Thank you for your suggestion. Yes, I use a message box to tell the user
> the content of textbox1 is invalid. I would prefer using a messsage box
> rather than a label. Is there a way I can detect within textbox1_Exit that
> the window is being closed, and therefore the message box should not be
> displayed?
>
> "Dave Peterson" <[email protected]> wrote in message
> news:[email protected]...
> > Are you using a msgbox to tell the user that the textbox is invalid? If
> yes,
> > maybe you could move that message to a label right near the textbox.
> >
> > If the user closes the userform, the label gets updated, but it's pretty
> quick
> > and probably not noticeable. But the good thing is that it won't disturb
> the
> > user with another msgbox.
> >
> > Old Car wrote:
> > >
> > > Thanks. Using that technique, I find that closing the window causes
> that
> > > event to fire. Is there a way to test for the fact that the Exit event
> is
> > > firing because a "Close" button is being selected?
> > >
> > > "Patrick Molloy" <[email protected]> wrote in
> message
> > > news:[email protected]...
> > > > you can use the textbox's Exit event...
> > > >
> > > > Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
> > > > If TextBox1.Text = "X" Then
> > > > Cancel = True
> > > > End If
> > > > End Sub
> > > >
> > > > "Old Car" wrote:
> > > >
> > > > > I have a form with some textboxes on it (textbox1 and textbox2).
> When
> > > the
> > > > > user tabs from textbox1 to the textbox2, I validate the contents of
> the
> > > > > textbox1 in the textbox1_AfterUpdate event handler. If the content
> > > textbox1
> > > > > is invalid, I want to keep the cursor in textbox1, rather than
> tabbing
> > > to
> > > > > textbox2.
> > > > >
> > > > > I have tried using textbox1.SetFocus method in the
> textbox1_AfterUpdate
> > > and
> > > > > textbox2_Enter event handlers. Neither worked.
> > > > >
> > > > > What is a good way to do this? Thanks.
> > > > >
> > > > >
> > > > >
> >
> > --
> >
> > Dave Peterson

--

Dave Peterson

Yes, it worked. Thanks to you and everyone else who helped.

"Dave Peterson" <[email protected]> wrote in message
news:[email protected]...
> This might work...
>
> Option Explicit
> Dim BlkProc As Boolean
> Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
> If BlkProc = True Then Exit Sub
> If IsNumeric(Me.TextBox1.Value) Then
> MsgBox "nope"
> Cancel = True
> End If
> End Sub
> Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
> If CloseMode = vbFormControlMenu Then
> BlkProc = True
> End If
> End Sub
>
>
> Old Car wrote:
> >
> > Thank you for your suggestion. Yes, I use a message box to tell the
user
> > the content of textbox1 is invalid. I would prefer using a messsage box
> > rather than a label. Is there a way I can detect within textbox1_Exit
that
> > the window is being closed, and therefore the message box should not be
> > displayed?
> >
> > "Dave Peterson" <[email protected]> wrote in message
> > news:[email protected]...
> > > Are you using a msgbox to tell the user that the textbox is invalid?
If
> > yes,
> > > maybe you could move that message to a label right near the textbox.
> > >
> > > If the user closes the userform, the label gets updated, but it's
pretty
> > quick
> > > and probably not noticeable. But the good thing is that it won't
disturb
> > the
> > > user with another msgbox.
> > >
> > > Old Car wrote:
> > > >
> > > > Thanks. Using that technique, I find that closing the window
causes
> > that
> > > > event to fire. Is there a way to test for the fact that the Exit
event
> > is
> > > > firing because a "Close" button is being selected?
> > > >
> > > > "Patrick Molloy" <[email protected]> wrote in
> > message
> > > > news:[email protected]...
> > > > > you can use the textbox's Exit event...
> > > > >
> > > > > Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
> > > > > If TextBox1.Text = "X" Then
> > > > > Cancel = True
> > > > > End If
> > > > > End Sub
> > > > >
> > > > > "Old Car" wrote:
> > > > >
> > > > > > I have a form with some textboxes on it (textbox1 and textbox2).
> > When
> > > > the
> > > > > > user tabs from textbox1 to the textbox2, I validate the contents
of
> > the
> > > > > > textbox1 in the textbox1_AfterUpdate event handler. If the
content
> > > > textbox1
> > > > > > is invalid, I want to keep the cursor in textbox1, rather than
> > tabbing
> > > > to
> > > > > > textbox2.
> > > > > >
> > > > > > I have tried using textbox1.SetFocus method in the
> > textbox1_AfterUpdate
> > > > and
> > > > > > textbox2_Enter event handlers. Neither worked.
> > > > > >
> > > > > > What is a good way to do this? Thanks.
> > > > > >
> > > > > >
> > > > > >
> > >
> > > --
> > >
> > > Dave Peterson
>
> --
>
> Dave Peterson