G'day there again, One & All.

I'm back again with the latest problem to catch me out.

I have a simple userform with 2 Texboxes (TextBox1 & TextBox2)
which are used to enter string values to 2 cells on my sheet. That bit
works fine, but I also have a CommandButton titled "Clear" which is
supposed to clear the values and enter null strings ("") to those cells.

The command button does what I want, however I need to click it
twice to clear the two textboxes & cells. The first click clears the
first textbox, and the second click clears the next. The code I'm using
is this:

Private Sub CommandButton3_Click()

With UserForm1
.TextBox1.Value = ""
.TextBox2.Value = ""
End With

End Sub

I've tried different variations including:

Private Sub CommandButton3_Click()

Dim ctrl As Control
For Each ctrl In Controls
If Left(ctrl.Name, 7) = "TextBox" Then
ctrl.Default = ""
End If
Next

End Sub

(This gave a type mismatch error at the ctrl.Default = "" line,
which surprised me a little as the Help file tells me that .Value is the
default for a textbox).

I think the 'With' routine is probably the better way to go, but I
can't figure out why I need one click per textbox.

Has anyone come across this before? or am I missing something so
obvious I should hide under the bedclothes to avoid the embarassment?

See ya
And thanks for all the help I keep getting

Ken McLennan
Qld, Australia

Ken,

I was surprised that you needed two clicks.
To test your problem, I created a form with two text boxes and a command
button to clear them using similar code to that which you supplied (I didn't
use a With construct but that worked just the same anyway);
Private Sub CommandButton1_Click()
TextBox1.Value = ""
TextBox2.Value = ""
End Sub
This cleared both text boxes with one click.
Have you stepped through the macro with the debugger?
Perhaps something is happening when your code is entering null strings to
the spreadsheet cells.


Ken McLennan wrote in message
.. .
G'day there again, One & All.

I'm back again with the latest problem to catch me out.

I have a simple userform with 2 Texboxes (TextBox1 & TextBox2)
which are used to enter string values to 2 cells on my sheet. That bit
works fine, but I also have a CommandButton titled "Clear" which is
supposed to clear the values and enter null strings ("") to those cells.

The command button does what I want, however I need to click it
twice to clear the two textboxes & cells. The first click clears the
first textbox, and the second click clears the next. The code I'm using
is this:

Private Sub CommandButton3_Click()

With UserForm1
.TextBox1.Value = ""
.TextBox2.Value = ""
End With

End Sub

I've tried different variations including:

Private Sub CommandButton3_Click()

Dim ctrl As Control
For Each ctrl In Controls
If Left(ctrl.Name, 7) = "TextBox" Then
ctrl.Default = ""
End If
Next

End Sub

(This gave a type mismatch error at the ctrl.Default = "" line,
which surprised me a little as the Help file tells me that .Value is the
default for a textbox).

I think the 'With' routine is probably the better way to go, but I
can't figure out why I need one click per textbox.

Has anyone come across this before? or am I missing something so
obvious I should hide under the bedclothes to avoid the embarassment?

See ya
And thanks for all the help I keep getting

Ken McLennan
Qld, Australia

G'day there Again,

I've received a response to this on DevelopersDex.com, from Mudd,
which doesn't appear to have caught up here as yet. Mudd tells me:

--------------------------------------------
I was surprised that you needed two clicks.
To test your problem, I created a form with two text boxes and a command
button to clear them using similar code to that which you supplied (I
didn't
use a With construct but that worked just the same anyway);
Private Sub CommandButton1_Click()
TextBox1.Value = ""
TextBox2.Value = ""
End Sub
This cleared both text boxes with one click.
Have you stepped through the macro with the debugger?
Perhaps something is happening when your code is entering null strings
to
the spreadsheet cells.
--------------------------------------------


I tried this approach, but without any change. I then put a MsgBox
between the 2 statements, and that worked ok (naturally, I had to click
on the msgbox, but the two text boxes cleared on one click.

Next I put a delay in:

TextBox1.Value = ""
Application.Wait Now + TimeValue("00:00:10")
TextBox2.Value = ""

This worked fine, except that now the first textbox didn't clear
at all, only the second one.

It seems that there's a delay in the processing of the worksheet.

I'm assigning the values of the text boxes to named ranges (single
cell each) on my sheet. The contents of these cells are then referred to
by displayed cells with an IF function to show a blank, not a zero, if
there is nothing the

=IF(ISBLANK(District),""," "&District)

I've also tried removing this function, and just assigning the
textbox contents to a cell without the named range - $R$1.

I still get the same result, I need 2 clicks to clear the entries.

It's most annoying.

See ya
Ken McLennan
Qld, Australia

Ken,

You have an unerring knack of making simple things complicated<vbg

I have replicated Mudd's test, but with your new information. I still don't
get your problem. There must be something else you haven't told us yet.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Ken McLennan" wrote in message
.. .
G'day there Again,

I've received a response to this on DevelopersDex.com, from Mudd,
which doesn't appear to have caught up here as yet. Mudd tells me:

--------------------------------------------
I was surprised that you needed two clicks.
To test your problem, I created a form with two text boxes and a command
button to clear them using similar code to that which you supplied (I
didn't
use a With construct but that worked just the same anyway);
Private Sub CommandButton1_Click()
TextBox1.Value = ""
TextBox2.Value = ""
End Sub
This cleared both text boxes with one click.
Have you stepped through the macro with the debugger?
Perhaps something is happening when your code is entering null strings
to
the spreadsheet cells.
--------------------------------------------


I tried this approach, but without any change. I then put a MsgBox
between the 2 statements, and that worked ok (naturally, I had to click
on the msgbox, but the two text boxes cleared on one click.

Next I put a delay in:

TextBox1.Value = ""
Application.Wait Now + TimeValue("00:00:10")
TextBox2.Value = ""

This worked fine, except that now the first textbox didn't clear
at all, only the second one.

It seems that there's a delay in the processing of the worksheet.

I'm assigning the values of the text boxes to named ranges (single
cell each) on my sheet. The contents of these cells are then referred to
by displayed cells with an IF function to show a blank, not a zero, if
there is nothing the

=IF(ISBLANK(District),""," "&District)

I've also tried removing this function, and just assigning the
textbox contents to a cell without the named range - $R$1.

I still get the same result, I need 2 clicks to clear the entries.

It's most annoying.

See ya
Ken McLennan
Qld, Australia

Try

Private Sub CommandButton3_Click()

Range("Distict").Clearcontents
Range("OtherName').ClearContents

End Sub


Do you have any events defined for the textboxes such as Change or Click?

Default means that if you did

.Textbox1 = ""

then excel would interpret this to mean
.Textbox1.Text = ""

--
Regards,
Tom Ogilvy



"Ken McLennan" wrote in message
.. .
G'day there again, One & All.

I'm back again with the latest problem to catch me out.

I have a simple userform with 2 Texboxes (TextBox1 & TextBox2)
which are used to enter string values to 2 cells on my sheet. That bit
works fine, but I also have a CommandButton titled "Clear" which is
supposed to clear the values and enter null strings ("") to those cells.

The command button does what I want, however I need to click it
twice to clear the two textboxes & cells. The first click clears the
first textbox, and the second click clears the next. The code I'm using
is this:

Private Sub CommandButton3_Click()

With UserForm1
.TextBox1.Value = ""
.TextBox2.Value = ""
End With

End Sub

I've tried different variations including:

Private Sub CommandButton3_Click()

Dim ctrl As Control
For Each ctrl In Controls
If Left(ctrl.Name, 7) = "TextBox" Then
ctrl.Default = ""
End If
Next

End Sub

(This gave a type mismatch error at the ctrl.Default = "" line,
which surprised me a little as the Help file tells me that .Value is the
default for a textbox).

I think the 'With' routine is probably the better way to go, but I
can't figure out why I need one click per textbox.

Has anyone come across this before? or am I missing something so
obvious I should hide under the bedclothes to avoid the embarassment?

See ya
And thanks for all the help I keep getting

Ken McLennan
Qld, Australia

G'day there Bob,

You have an unerring knack of making simple things complicated<vbg

heeheechucklechortle. I'm certainly not going to argue with you
there <g

I have replicated Mudd's test, but with your new information. I still don't
get your problem. There must be something else you haven't told us yet.

Well, I've not told anyone here how remarkably good looking I am,
but I don't think that's what you meant =)

I don't really know what to tell you. The only thing that I can
think of follows this chain of logic:

My worksheet has several (about 5 or 6) SumProduct formulae, plus
about 30 others. Perhaps all the calculations are slowing down the
processing of my VB statements in such a way that the second one is
"forgotten" by the time it works through all those formulae on the
sheet. I don't really think that's it, (I'm using an Athlon K7 at 2.1
GHz with 1 Gig of ram), but I can't think of anything else that it might
be.

I tried my code on a blank worksheet in the office & there wasn't
a problem. But, as expected, on my home computer with the worksheet I'm
working on I had the same result again. I can't think of anything else
that would make it work like this.

See ya
Ken McLennan
Qld, Australia.

G'day there Tom,

Private Sub CommandButton3_Click()

Range("Distict").Clearcontents
Range("OtherName').ClearContents

End Sub

It worked!! That approach has done the trick!!

I don't suppose you can think of any reason why that works and the
way I tried first off didn't? I don't know how XL calculates these
things, so if you know can you please elucidate that I might understand
just a tad of what I'm doing?

Do you have any events defined for the textboxes such as Change or Click?

No, none at all.

Default means that if you did

.Textbox1 = ""

then excel would interpret this to mean
.Textbox1.Text = ""

Hmm... quoting from the help file:

The default property for a TextBox is the Value property.

So in this instance, is the .Text property equal to the .Value
property because Textboxes deal with text?

Thanks very much for your assistance,
Ken McLennan
Qld, Australia

the text and value properties are identical.

--
Regards,
Tom Ogilvy

"Ken McLennan" wrote in message
.. .
G'day there Tom,

Private Sub CommandButton3_Click()

Range("Distict").Clearcontents
Range("OtherName').ClearContents

End Sub

It worked!! That approach has done the trick!!

I don't suppose you can think of any reason why that works and the
way I tried first off didn't? I don't know how XL calculates these
things, so if you know can you please elucidate that I might understand
just a tad of what I'm doing?

Do you have any events defined for the textboxes such as Change or
Click?

No, none at all.

Default means that if you did

.Textbox1 = ""

then excel would interpret this to mean
.Textbox1.Text = ""

Hmm... quoting from the help file:

The default property for a TextBox is the Value property.

So in this instance, is the .Text property equal to the .Value
property because Textboxes deal with text?

Thanks very much for your assistance,
Ken McLennan
Qld, Australia

G'day there Tom,

the text and value properties are identical.

Thought so. Thanks for clarifying.

See ya
Ken McLennan
Qld, Australia