ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   How to loop through controls on a MultiPage (https://www.excelbanter.com/excel-programming/322956-how-loop-through-controls-multipage.html)

42N83W
How to loop through controls on a MultiPage
 
Windows XP Pro SP
Excel 2002 SP 2

I have 17 comboboxs on Page4 of MultiPage1 on a userform. How can I loop
through them to check a property without having to put 17 checks inside my
loop?

Thanks!

-gk-

--
=================================================
The creative act is not the province of remote oracles or rarefied geniuses
but a transparent process that is open to everyone.
-Greg Kot in Wilco Learning How To Die-



42N83W
How to loop through controls on a MultiPage
 

"42N83W" wrote in message
...
Windows XP Pro SP
Excel 2002 SP 2

I have 17 comboboxs on Page4 of MultiPage1 on a userform. How can I loop
through them to check a property without having to put 17 checks inside my
loop?

Thanks!

-gk-


I've tried this

Dim cbx As ComboBox 'also tried Dim cbx As Control

For Each cbx In frmMain.fra02_01 'fra02_01 is a frame on MultiPage1(4)
'also tried For Each cbx In
frmMain.MultiPage1(4)
If cbx.Value = "" Then flag1 = True
Next cbx

I've tried other combinations besides the above, and I keep getting a "This
Property is not supported"



-gk-



Jim Cone
How to loop through controls on a MultiPage
 
42...

Something similar to the following ought to work...

Dim objCB As MSForms.ComboBox
For Each objCB In UserForm1.Frame1.Controls
objCB.Enabled = True
Next

Jim Cone
San Francisco, USA


"42N83W" wrote in message
...

"42N83W" wrote in message
...
Windows XP Pro SP
Excel 2002 SP 2
I have 17 comboboxs on Page4 of MultiPage1 on a userform. How can I loop
through them to check a property without having to put 17 checks inside my
loop?
Thanks!
-gk-
I've tried this
Dim cbx As ComboBox 'also tried Dim cbx As Control

For Each cbx In frmMain.fra02_01 'fra02_01 is a frame on MultiPage1(4)
'also tried For Each cbx In frmMain.MultiPage1(4)
If cbx.Value = "" Then flag1 = True
Next cbx

I've tried other combinations besides the above, and I keep getting a "This
Property is not supported"
-gk-


42N83W
How to loop through controls on a MultiPage
 

"Jim Cone" wrote in message
...
42...

Something similar to the following ought to work...

Dim objCB As MSForms.ComboBox
For Each objCB In UserForm1.Frame1.Controls
objCB.Enabled = True
Next

Jim Cone
San Francisco, USA


Now I'm getting a Type-Mismatch error.

Hmmmmm

-gk-

"42N83W" wrote in message
...

"42N83W" wrote in message
...
Windows XP Pro SP
Excel 2002 SP 2
I have 17 comboboxs on Page4 of MultiPage1 on a userform. How can I
loop
through them to check a property without having to put 17 checks inside
my
loop?
Thanks!
-gk-
I've tried this
Dim cbx As ComboBox 'also tried Dim cbx As Control

For Each cbx In frmMain.fra02_01 'fra02_01 is a frame on MultiPage1(4)
'also tried For Each cbx In frmMain.MultiPage1(4)
If cbx.Value = "" Then flag1 = True
Next cbx

I've tried other combinations besides the above, and I keep getting a
"This
Property is not supported"
-gk-

Jim Cone
How to loop through controls on a MultiPage
 
42...

Show us the code and where the error occurs.

Jim cone
San Francisco, USA


"42N83W" wrote in message
...

"Jim Cone" wrote in message
...
42...
Something similar to the following ought to work...
Dim objCB As MSForms.ComboBox
For Each objCB In UserForm1.Frame1.Controls
objCB.Enabled = True
Next
Jim Cone
San Francisco, USA

Now I'm getting a Type-Mismatch error.
Hmmmmm
-gk-

42N83W
How to loop through controls on a MultiPage
 
Option Explicit

Dim cbx as MSForms.ComboBox

' flag1 is a global variable

Public Sub ValidateFiles()
' called from cmd02_Next_Click() in frmMain code module

'error Run Time 13 Type Mismatch occurs in the For statement
For Each cbx In frmMain.fra02_01.Controls
If cbx.Value = "" Then
'cbx.Text doesn't work
flag1 = True
End If
Next cbx

End Sub



"Jim Cone" wrote in message
...
42...

Show us the code and where the error occurs.

Jim cone
San Francisco, USA


"42N83W" wrote in message
...

"Jim Cone" wrote in message
...
42...
Something similar to the following ought to work...
Dim objCB As MSForms.ComboBox
For Each objCB In UserForm1.Frame1.Controls
objCB.Enabled = True
Next
Jim Cone
San Francisco, USA

Now I'm getting a Type-Mismatch error.
Hmmmmm
-gk-

Jim Cone
How to loop through controls on a MultiPage
 
Sub Test()
Dim objCB As MSForms.ComboBox

For Each objCB In UserForm1.Frame1.Controls
objCB.AddItem "100"
If objCB.Column(0, 0) = "" Then
MsgBox True
Else
MsgBox False
End If
Next
End Sub


"42N83W" wrote in message
...
Option Explicit

Dim cbx as MSForms.ComboBox

' flag1 is a global variable

Public Sub ValidateFiles()
' called from cmd02_Next_Click() in frmMain code module

'error Run Time 13 Type Mismatch occurs in the For statement
For Each cbx In frmMain.fra02_01.Controls
If cbx.Value = "" Then
'cbx.Text doesn't work
flag1 = True
End If
Next cbx

End Sub



"Jim Cone" wrote in message
...
42...

Show us the code and where the error occurs.

Jim cone
San Francisco, USA


"42N83W" wrote in message
...

"Jim Cone" wrote in message
...
42...
Something similar to the following ought to work...
Dim objCB As MSForms.ComboBox
For Each objCB In UserForm1.Frame1.Controls
objCB.Enabled = True
Next
Jim Cone
San Francisco, USA

Now I'm getting a Type-Mismatch error.
Hmmmmm
-gk-

42N83W
How to loop through controls on a MultiPage
 
Still getting a type mismatch in the FOR statement.

This worked, but only because my labels weren't blank:

For i = 0 To fra02_01.Controls.Count - 1
If fra02_01.Controls.Item(i) = "" Then
' do stuff
Exit Sub
End If
Next i


-gk-


"Jim Cone" wrote in message
...
Sub Test()
Dim objCB As MSForms.ComboBox

For Each objCB In UserForm1.Frame1.Controls
objCB.AddItem "100"
If objCB.Column(0, 0) = "" Then
MsgBox True
Else
MsgBox False
End If
Next
End Sub


"42N83W" wrote in message
...
Option Explicit

Dim cbx as MSForms.ComboBox

' flag1 is a global variable

Public Sub ValidateFiles()
' called from cmd02_Next_Click() in frmMain code module

'error Run Time 13 Type Mismatch occurs in the For statement
For Each cbx In frmMain.fra02_01.Controls
If cbx.Value = "" Then
'cbx.Text doesn't work
flag1 = True
End If
Next cbx

End Sub



"Jim Cone" wrote in message
...
42...

Show us the code and where the error occurs.

Jim cone
San Francisco, USA


"42N83W" wrote in message
...

"Jim Cone" wrote in message
...
42...
Something similar to the following ought to work...
Dim objCB As MSForms.ComboBox
For Each objCB In UserForm1.Frame1.Controls
objCB.Enabled = True
Next
Jim Cone
San Francisco, USA

Now I'm getting a Type-Mismatch error.
Hmmmmm
-gk-

Tom Ogilvy
How to loop through controls on a MultiPage
 
Private Sub CommandButton1_Click()
For Each ctrl In Me.MultiPage1.Pages(3).Controls
If TypeOf ctrl Is MSForms.ComboBox Then
msgbox ctrl.Name & ": " & ctrl.Value
End If
Next
End Sub

worked fine for me

--
Regards,
Tom Ogilvy



"42N83W" wrote in message
...
Still getting a type mismatch in the FOR statement.

This worked, but only because my labels weren't blank:

For i = 0 To fra02_01.Controls.Count - 1
If fra02_01.Controls.Item(i) = "" Then
' do stuff
Exit Sub
End If
Next i


-gk-


"Jim Cone" wrote in message
...
Sub Test()
Dim objCB As MSForms.ComboBox

For Each objCB In UserForm1.Frame1.Controls
objCB.AddItem "100"
If objCB.Column(0, 0) = "" Then
MsgBox True
Else
MsgBox False
End If
Next
End Sub


"42N83W" wrote in message
...
Option Explicit

Dim cbx as MSForms.ComboBox

' flag1 is a global variable

Public Sub ValidateFiles()
' called from cmd02_Next_Click() in frmMain code module

'error Run Time 13 Type Mismatch occurs in the For statement
For Each cbx In frmMain.fra02_01.Controls
If cbx.Value = "" Then
'cbx.Text doesn't work
flag1 = True
End If
Next cbx

End Sub



"Jim Cone" wrote in message
...
42...

Show us the code and where the error occurs.

Jim cone
San Francisco, USA


"42N83W" wrote in message
...

"Jim Cone" wrote in message
...
42...
Something similar to the following ought to work...
Dim objCB As MSForms.ComboBox
For Each objCB In UserForm1.Frame1.Controls
objCB.Enabled = True
Next
Jim Cone
San Francisco, USA

Now I'm getting a Type-Mismatch error.
Hmmmmm
-gk-

Jim Cone
How to loop through controls on a MultiPage
 
"On final approach"...

Sub Test()
Dim x As MSForms.Control

For Each x In UserForm1.Frame1.Controls
If TypeOf x Is MSForms.Label Then
If x.Caption = "Label1" Then
MsgBox "Label1 height is " & x.Height
Else
MsgBox "Caption is not Label1"
End If

ElseIf TypeOf x Is MSForms.ComboBox Then
If x.Name = "ComboBox1" Then
MsgBox "ComboBox1"
Else
MsgBox "Not ComboBox1"
End If

ElseIf TypeOf x is...

End If
Next

End Sub


"42N83W" wrote in message
...
Still getting a type mismatch in the FOR statement.

This worked, but only because my labels weren't blank:

For i = 0 To fra02_01.Controls.Count - 1
If fra02_01.Controls.Item(i) = "" Then
' do stuff
Exit Sub
End If
Next i


-gk-

Tom Ogilvy
How to loop through controls on a MultiPage
 
Left out the declaration. Declare ctrl as Control

Private Sub CommandButton1_Click()
Dim ctrl as Control
For Each ctrl In Me.MultiPage1.Pages(3).Controls
If TypeOf ctrl Is MSForms.ComboBox Then
msgbox ctrl.Name & ": " & ctrl.Value
End If
Next
End Sub

--
Regards,
Tom Ogilvy


"Tom Ogilvy" wrote in message
...
Private Sub CommandButton1_Click()
For Each ctrl In Me.MultiPage1.Pages(3).Controls
If TypeOf ctrl Is MSForms.ComboBox Then
msgbox ctrl.Name & ": " & ctrl.Value
End If
Next
End Sub

worked fine for me

--
Regards,
Tom Ogilvy



"42N83W" wrote in message
...
Still getting a type mismatch in the FOR statement.

This worked, but only because my labels weren't blank:

For i = 0 To fra02_01.Controls.Count - 1
If fra02_01.Controls.Item(i) = "" Then
' do stuff
Exit Sub
End If
Next i


-gk-


"Jim Cone" wrote in message
...
Sub Test()
Dim objCB As MSForms.ComboBox

For Each objCB In UserForm1.Frame1.Controls
objCB.AddItem "100"
If objCB.Column(0, 0) = "" Then
MsgBox True
Else
MsgBox False
End If
Next
End Sub


"42N83W" wrote in message
...
Option Explicit

Dim cbx as MSForms.ComboBox

' flag1 is a global variable

Public Sub ValidateFiles()
' called from cmd02_Next_Click() in frmMain code module

'error Run Time 13 Type Mismatch occurs in the For statement
For Each cbx In frmMain.fra02_01.Controls
If cbx.Value = "" Then
'cbx.Text doesn't work
flag1 = True
End If
Next cbx

End Sub



"Jim Cone" wrote in message
...
42...

Show us the code and where the error occurs.

Jim cone
San Francisco, USA


"42N83W" wrote in message
...

"Jim Cone" wrote in message
...
42...
Something similar to the following ought to work...
Dim objCB As MSForms.ComboBox
For Each objCB In UserForm1.Frame1.Controls
objCB.Enabled = True
Next
Jim Cone
San Francisco, USA

Now I'm getting a Type-Mismatch error.
Hmmmmm
-gk-

39N 95W
How to loop through controls on a MultiPage
 
Tom and Jim,

Thanks!

Yes, it was the TypeOf that did the trick. Thanks for your patience!!

-gk-





"Tom Ogilvy" wrote in message
...
Private Sub CommandButton1_Click()
For Each ctrl In Me.MultiPage1.Pages(3).Controls
If TypeOf ctrl Is MSForms.ComboBox Then
msgbox ctrl.Name & ": " & ctrl.Value
End If
Next
End Sub

worked fine for me

--
Regards,
Tom Ogilvy



"42N83W" wrote in message
...
Still getting a type mismatch in the FOR statement.

This worked, but only because my labels weren't blank:

For i = 0 To fra02_01.Controls.Count - 1
If fra02_01.Controls.Item(i) = "" Then
' do stuff
Exit Sub
End If
Next i


-gk-


"Jim Cone" wrote in message
...
Sub Test()
Dim objCB As MSForms.ComboBox

For Each objCB In UserForm1.Frame1.Controls
objCB.AddItem "100"
If objCB.Column(0, 0) = "" Then
MsgBox True
Else
MsgBox False
End If
Next
End Sub


"42N83W" wrote in message
...
Option Explicit

Dim cbx as MSForms.ComboBox

' flag1 is a global variable

Public Sub ValidateFiles()
' called from cmd02_Next_Click() in frmMain code module

'error Run Time 13 Type Mismatch occurs in the For statement
For Each cbx In frmMain.fra02_01.Controls
If cbx.Value = "" Then
'cbx.Text doesn't work
flag1 = True
End If
Next cbx

End Sub



"Jim Cone" wrote in message
...
42...

Show us the code and where the error occurs.

Jim cone
San Francisco, USA


"42N83W" wrote in message
...

"Jim Cone" wrote in message
...
42...
Something similar to the following ought to work...
Dim objCB As MSForms.ComboBox
For Each objCB In UserForm1.Frame1.Controls
objCB.Enabled = True
Next
Jim Cone
San Francisco, USA

Now I'm getting a Type-Mismatch error.
Hmmmmm
-gk-


All times are GMT +1. The time now is 07:32 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com