variable name

[Evan]

Hi,

I have a user form containing several text boxes (AA,BB,CC), etc. I want to
set variables (AAvar,BBvar,CCvar) to the values of those text boxes. I am
looking for a way to cycle through the textboxes and set the given variable
based on the control name. Such as:

For Each ctrl In Me.Controls
If TypeOf ctrl Is MSForms.TextBox Then
With ctrl
Set Cstr(.name) & "var" = ctrl.value
......

Obviously this method doesnt work but hopefully explains what I am
after...any technique to use the control name to refer to a similiarly named
variable

Thanks

 

[Jim Cone]

Evan,
If you add the textboxes to a Frame control then this will work...
'---------------------------------
Dim ctrl As MSForms.TextBox
For Each ctrl In Frame1.Controls
If TypeOf ctrl Is MSForms.TextBox Then
MsgBox ctrl.Name
End If
Next
Set ctrl = Nothing
'---------------------------------
Jim Cone
San Francisco, USA


Hi,
I have a user form containing several text boxes (AA,BB,CC), etc. I want to
set variables (AAvar,BBvar,CCvar) to the values of those text boxes. I am
looking for a way to cycle through the textboxes and set the given variable
based on the control name. Such as:
For Each ctrl In Me.Controls
If TypeOf ctrl Is MSForms.TextBox Then
With ctrl
Set Cstr(.name) & "var" = ctrl.value.....

Obviously this method doesnt work but hopefully explains what I am
after...any technique to use the control name to refer to a similiarly named
variable
Thanks

 

[Evan]

Thanks, but what I am looking for is how to programatically determine the
name of the variable to set based on the name of the control.

Instead of saying AAvar = AA.value I want to programatically choose which
public variable (that has already been declared) to use by appending "var"
to the name of the control. Something like control.name & "var" =
control.value. I may not be making myself clear, but in a I am looking for a
technique or alternative to be able to use a generic function that can
determine what variable to set based on the name of the control.

 

[Jim Cone]

Private Sub CommandButton2_Click()
Dim Ctrl As MSForms.TextBox
Dim AAvar As String
Dim BBvar As String
Dim CCvar As String

For Each Ctrl In Frame1.Controls
If TypeOf Ctrl Is MSForms.TextBox Then
Select Case Ctrl.Name
Case "AA"
AAvar = Ctrl.Value
Case "BB"
BBvar = Ctrl.Value
Case "CC"
CCvar = Ctrl.Value
End Select
End If
Next
MsgBox AAvar & vbCr & BBvar & vbCr & CCvar
Set Ctrl = Nothing
End Sub
'------------------------

"Evan" <[email protected]>
wrote in message
Thanks, but what I am looking for is how to programatically determine the
name of the variable to set based on the name of the control.
Instead of saying AAvar = AA.value I want to programatically choose which
public variable (that has already been declared) to use by appending "var"
to the name of the control. Something like control.name & "var" =
control.value. I may not be making myself clear, but in a I am looking for a
technique or alternative to be able to use a generic function that can
determine what variable to set based on the name of the control.

 

[Dave Peterson]

VBA doesn't support that kind of variable.

You may be able to do something with:

select case lcase(ctrl.name)
case is = "aa": AAvar = ctrl.value
....

Or maybe make an array of these variables and keep track of them in that array.

 

[Evan]

Thanks, I was hoping to find a method that would preclude having to recode
when adding a control, but I do appreciate the suggestion.

As far as Arrays, I tried that before posting but got confused becuase I
frankly only have used them in the most basic sense. Would you mind
elaborating?

Thanks

 

[Dave Peterson]

Let's say you have 5 checkboxes (named checkbox1, checkbox2, checkbox3,
checkbox4 and checkbox5)

You could set up an array:

dim myCBXValues (1 to 5) as boolean
dim iCtr as long

for ictr = 1 to 5
mycbxvalues(ictr) = me.controls("Checkbox" & ictr).value
next ictr

---
Oops. Textboxes...

Dim myTBoxValues(1 To 5) As String
Dim iCtr As Long

For iCtr = 1 To 5
myTBoxValues(iCtr) = Me.Controls("textbox" & iCtr).Value
Next iCtr


Is this too basic?

 

[Guest]

Hi,

I trying to get the code you suggested to work in my macro which is in
module1 rather than on the form. The macro is called when the OK button is
pressed on the form.
My form is called Printform and the checkboxes names print1, print2 etc to
print24
I just want to check if each checkboxbox is ticked or not and if so print a
range

This code runs but areacheck remains false throughout even if the checkbox
is ticked
Dim chkboxvalues(1 To 24) As Boolean
For Area = 1 To 24
chkboxvalues(Area) = Printform.Controls("Print" & Area).Value
'test if chkboxvalues is true or false
'rest of macro was here
Next Area


I also tried this but it fails to run at all
Dim chkboxvalues(1 To 24) As Boolean
For Area = 1 To 24
chkboxvalues = Printform.("Print" & Area).Value
'test if chkboxvalues is true or false
'rest of macro was here
Next Area

Can you see where i'm going wrong?

 

[Guest]

Hi, please ignore previous post this is what i meant to say

I trying to get the code you suggested to work in my macro which is in
module1 rather than on the form. The macro is called when the OK button is
pressed on the form.
My form is called Printform and the checkboxes names print1, print2 etc to
print24
I just want to check if each checkboxbox is ticked or not and if so print a
range

This code runs but chkboxvalues(area) remains false throughout even if the
checkbox
is ticked
Dim chkboxvalues(1 To 24) As Boolean
For Area = 1 To 24
chkboxvalues(Area) = Printform.Controls("Print" & Area).Value
'test if chkboxvalues is true or false
'rest of macro was here
Next Area


I also tried this but it fails to run at all
Dim chkboxvalues(1 To 24) As Boolean
For Area = 1 To 24
chkboxvalues = Printform.("Print" & Area).Value
'test if chkboxvalues is true or false
'rest of macro was here
Next Area

Can you see where i'm going wrong?

 

[Dave Peterson]

I created a small userform with 5 checkboxes.

I had this code behind the userform:

Option Explicit
Private Sub CommandButton1_Click()
Call testme04
End Sub

I had this in a module:

Option Explicit
Sub testme04()
Dim iArea
Dim chkboxvalues(1 To 24) As Boolean
For iArea = 1 To 5 '24
chkboxvalues(iArea) = PrintForm.Controls("Print" & iArea).Value
'test if chkboxvalues is true or false
'rest of macro was here
Next iArea

For iArea = 1 To 5 '24
MsgBox chkboxvalues(iArea)
Next iArea
End Sub
Sub testme03()
PrintForm.Show
End Sub

It always showed the correct true/falses.

By the way, I don't like using variables that look like reserved names. I
changed Area to iArea. (I don't think it is the cause of your trouble,
though--it's just confusing to me.)

Hi, please ignore previous post this is what i meant to say

I trying to get the code you suggested to work in my macro which is in
module1 rather than on the form. The macro is called when the OK button is
pressed on the form.
My form is called Printform and the checkboxes names print1, print2 etc to
print24
I just want to check if each checkboxbox is ticked or not and if so print a
range

This code runs but chkboxvalues(area) remains false throughout even if the
checkbox
is ticked
Dim chkboxvalues(1 To 24) As Boolean
For Area = 1 To 24
chkboxvalues(Area) = Printform.Controls("Print" & Area).Value
'test if chkboxvalues is true or false
'rest of macro was here
Next Area

I also tried this but it fails to run at all
Dim chkboxvalues(1 To 24) As Boolean
For Area = 1 To 24
chkboxvalues = Printform.("Print" & Area).Value
'test if chkboxvalues is true or false
'rest of macro was here
Next Area

Can you see where i'm going wrong?