Excel VBA help 3

[ido233]

Hi all

I have a vba form with multipage form in it
the inputs on all 6 pages are the same i have it working fine but i have a lot of code for it is there a way to use a for next to populate each page.

Private Sub UserForm_Initialize()
txtSfcWnd1.Value = "" 'Surface Wind
txtCrossWnd1.Value = "" 'Cross Wind
txtVsby1.Value = "" 'Visibility
For i = 1 To 6
With cboFog(x) 'Fog
.AddItem "nil"
.AddItem "f-"
.AddItem "f"
.AddItem "f+"
End With
cboFog(x).Value = "nil"
Next
With cboIcg1 'Icing
.AddItem "nil"
.AddItem "i-"
.AddItem "i"
.AddItem "i+"
End With
cboIcg1.Value = "nil"

With cboTurb1 'Turbulance
.AddItem "nil"
.AddItem "t-"
.AddItem "t"
.AddItem "t+"
End With
cboTurb1.Value = "nil"


For i = 0 To 8: cboCldCov1.AddItem i: Next i ' Cloud

cboCldCov1.Value = "0" 'Cloud Cover

txtCig1.Value = "" 'Ceiling

With cboMPhase1 'MoonPhase
.AddItem "nil"
.AddItem "New Moon"
.AddItem "Full Moon"
End With
cboMPhase1.Value = "nil"

txtTemp1.Value = "" 'Temperature

With cboPrecip1 'Precipitation
.AddItem "nil"
.AddItem "p-"
.AddItem "p"
.AddItem "p+"
End With
cboPrecip1.Value = "nil"
End Sub

each page page has the same data except the names are different eg cboPrecip1.value for page 1
cboPrecip2.value for page 2 ect.

is there a way to cut this code down.

thanks ed

 

[combo]

What about this:

Dim vCurrentList() As Variant
vCurrentList = Array("nil", "f-", "f", "f+")
iValue = 1
Call FillList(Me.ComboBox1, 1, vCurrentList)
...
Private Sub FillList(ctrl As ComboBox, Index As Integer, vTheList() As Variant)
Dim vItem As Variant
With ctrl
    .List = vTheList
    .ListIndex = Index - 1
End With
End Sub

combo

 

[stevexff]

Here's a simple sub that sets or unsets any checkboxes it finds on all pages of a multipage. It's designed to be fired from a 'Check All' or 'Check None' button, but it should give you the idea of looping through the pages...

Private Sub setAll(check As Boolean)
    
    For Each p In MultiPage1.Pages
        For Each c In p.Controls
            If TypeName(c) = "CheckBox" Then
                c.Value = check
            End If
        Next
    Next
    
End Sub

Steve

[small]"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object:erlDesignPatterns)[/small]

 

[combo]

I left some unused code above, here's the cleaned one:

Dim vCurrentList() As Variant
vCurrentList = Array("nil", "f-", "f", "f+")
Call FillList(Me.ComboBox1, 1, vCurrentList)
...
Private Sub FillList(ctrl As ComboBox, Index As Integer, vTheList() As Variant)
With ctrl
    .List = vTheList
    .ListIndex = Index - 1
End With
End Sub

As Steve suggested, you can loop in controls combined with typename(..) and Name tests to properly set lists and values.

combo

 

[PHV]

I am amazed at the following:
For [!]i[/!] = 1 To 6
With cboFog([!]x[/!])

 

[ido233]

sorry for the wrong code the
For i = 1 To 6
With cboFog(x) 'Fog
should not be there it was something i was trying
the code that works is

Private Sub UserForm_Initialize()
    
    txtSfcWnd1.Value = "" 'Surface Wind
    txtCrossWnd1.Value = "" 'Cross Wind
    txtVsby1.Value = "" 'Visibility

With cboFog1'Fog
        .AddItem "nil"
        .AddItem "f-"
        .AddItem "f"
        .AddItem "f+"
End With
    cboFog1.Value = "nil"
Next
With cboIcg1 'Icing
        .AddItem "nil"
        .AddItem "i-"
        .AddItem "i"
        .AddItem "i+"
End With
    cboIcg1.Value = "nil"

With cboTurb1 'Turbulance
        .AddItem "nil"
        .AddItem "t-"
        .AddItem "t"
        .AddItem "t+"
End With
    cboTurb1.Value = "nil"


For i = 0 To 8: cboCldCov1.AddItem i: Next i  ' Cloud

    cboCldCov1.Value = "0" 'Cloud Cover

    txtCig1.Value = "" 'Ceiling

With cboMPhase1 'MoonPhase
        .AddItem "nil"
        .AddItem "New Moon"
        .AddItem "Full Moon"
End With
    cboMPhase1.Value = "nil"
    
    txtTemp1.Value = "" 'Temperature
    
With cboPrecip1 'Precipitation
        .AddItem "nil"
        .AddItem "p-"
        .AddItem "p"
        .AddItem "p+"
End With
    cboPrecip1.Value = "nil"
End Sub

ed

 

[PHV]

A starting point:
For i = 1 To 6
With Me.Controls("cboFog" & i) 'Fog
.AddItem "nil"
.AddItem "f-"
.AddItem "f"
.AddItem "f+"
.Value = "nil"
End With
...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886