Advance the value of a text box by one, one click at a time

[vannaLust]

HALP! I need some help understanding why my code gives me an error. basically, i want a command button to (on each click) to advance the value of (and what is shown) on a particular text box.

"txtProInstall" is the name text box.
"cmdAdd" is my command button.

here is the code i tried:

Private Sub cmdAdd_Click()
Dim txtProInstall As TextBox
Set txtProInstall.Value = txtProInstall.Value + 1
End Sub

to start out, this is a very simple form (in an excel app) with just these two things on it (besides a label for the text box), but i intend to add more of these, and also a button to declinate the value by one as well for the appropriate text box.

when i try to run the form, i get this error:

"Run-time error-91:
Object variable or With block variable not set"

any ideas on how to fix this? do i have to initialize the form with pre-set values first??

many thanks

 

[SkipVought]

Hi,

Try this...

Public idx
Function AddOne() As Integer
    idx = idx + 1
    AddOne = idx
End Function

Private Sub CommandButton1_Click()
        TextBox1.Value = AddOne
End Sub

Skip,
metzgsk@voughtaircraft.com

 

[vannaLust]

woo hoo! thanks, skip! this works just great. could you tell me what was wrong with what i wanted to do originally, conceptually? just so i can understand better for next time ;-)


thanks again

 

[SkipVought]

When you add objects to a form, they are objects within that form (as the parent object) and do not have to be defined in a Dim statement.

The Set statement is for the purpose of assigning an object. Say you define cntl as a Control. Then you could
Set cntl = Controls(2)
a particular control.

You were wanting to assign a value to a property of a control...
txtProInstall.Value = txtProInstall.Value + 1

BUT...
a textbox as a type is TEXT, so you would get a type missmatch.

So if you use a variable like this...
Private Sub CommandButton1_Click()
i = i + 1
TextBox1.Value = i
End Sub
it still does not work because this is a private sub and the i variable is new every time the click event occurs.

By making the variable Public to the worksheet, the value is held in working storage for Excel
Public i
Private Sub CommandButton1_Click()
i = i + 1
TextBox1.Value = i
End Sub
I just chose to put it in a function at the workbook level.
Hope this helps.


Skip,
metzgsk@voughtaircraft.com