Solved: Clearing a Userform

[Dav1000]

Hi all from a new member.

I have designed and coded a VBA program that utilizes a user-form as the data input/output interface when run. Below is abbreviated version of my code, where inputs are:

variable = TextBox#.Text (where # = 0 to 9)


and outputs are:

Label#.Caption =calculated value. (where # = 19 to 52)



This runs and works well. However, I am attempting to code in a second command button sub routine that clears the input and output boxes without the end user having to close the application. This is so the application can be run with multiple scenarios without closing and restarting the application ad naseum...

For the input text boxes this works when I use the code:

Textbox#.Value = Null.


But, When I attempt the same with the label#.Caption = null I get:

run-time error '94';

Invalid use of null.


I have tried Label.Value and Label.Text which are not recognized by VBA as valid data members.


However, When I use Label#.Caption = 0 in the subroutine it does reset the output labels to "0".

My question is( since I have not found the answer in any other resources), do any of you know if what I am attempting is the correct method or is it even possible to clear a Label used as an output on a userform using a command button subroutine?

Thank you.


Dav1000

[Simon Lloyd]

You would be better of using...=VBNullstring or ...="", if you are clearing all captions you could possible use (not tried or tested)

For i = 1 to me.labels.count
me.label & i & .caption = vbnullstring 'or you can use =""
next i

[GTO]

Greetings Dav1000,

Well it looks like I get the privilege of being the first member to say "Welcome," and in my case, a friendly "Howdy" from Arizona.

As to your question (leastwise to my just-woke-up-late brain's understanding), clearing textboxes and/or labels is no problem. With Textboxes, you can use the .Text or .Value property. I usually just stick w/.Value, just so as I don't get confused later and try and write the .Text property to a cell (which is read-only).
Anyways, there is no .Value property to a Label, so you need to use the Caption property.
By (hopefully sensible) example:

Option Explicit

Private Sub cmdCalc_Click()
With Me
.Label1.Caption = Val(.TextBox1) * 3
.Label2.Caption = Val(.TextBox2) + Val(.TextBox1)
.Label3.Caption = Val(.TextBox3) * 10.1
End With
End Sub

Private Sub cmdClear_Click()
With Me
.Label1.Caption = vbNullString
'or
.Label2.Caption = ""
.Label3.Caption = ""

.TextBox1.Value = vbNullString
'or
.TextBox2.Value = ""
'or
.TextBox3.Text = vbNullString
End With
End Sub
Private Sub UserForm_Initialize()

With Me
.Label1.Caption = vbNullString
'or
.Label2.Caption = ""
.Label3.Caption = ""

.TextBox1.Value = vbNullString
'or
.TextBox2.Value = ""
'or
.TextBox3.Text = vbNullString
End With
End Sub


Does that help?

Mark

[Dav1000]

Thanks for the help guys. Both empty quotes and vbNullString work.


Dav1000