Array from Checkbox

[tmischler]

Hi there,

I am trying to define an array depending on what checkboxes are ticked - ie. if user ticks a & b the array is "bacon" and "tomatoes" but if they tick c & d, the array is "eggs" and "cheese".

Does anyone know how to do this?

 

[tmischler]

Ok, to be more specific, here is a piece of the problem code.
The problem is that some of the variables in myNames are set to "". What I want is for the array to not include "" values. Does anyone know how I could do this?

Dim myNames() As Variant
myNames = Array(CBFM, CCB, SF, CENTRE, FM, GCH)

If OptOpenFile.Value = True Then
MsgBox "This will not refresh files automatically"
For i = LBound(myNames) To UBound(myNames)
Workbooks.Open Filename:=mypath & myNames(i) & ".xls"
Next i
Else
End If

 

[tusharm]

So, why not include a IF statement that executes the Workbooks.Open only if entry i is not ""?

Ok, to be more specific, here is a piece of the problem code.
The problem is that some of the variables in myNames are set to "". What I want is for the array to not include "" values. Does anyone know how I could do this?
{snip}

 

[tmischler]

I did try but I can't seem to get it to work - I ended up with too many 'next's and not enough 'for's. How would you put the if statement in?

 

[tmischler]

Ah - I think it's because i is a count of the number of items in the array. Below is the whole code:

Private Sub CmdOK_Click()

Dim mypath As String
Dim i As Long
Dim CBFM As String
Dim CCB As String
Dim SF As String
Dim CENTRE As String
Dim FM As String
Dim GCH As String

mypath = "L:\CBFM Decision Support & Balance Sheet Mgt\2004 Full Year Results\Balance Sheet\Geographies\"

If ChbCBFM.Value = True Then
CBFM = "CBFM"
Else: CBFM = ""
End If

If ChbCCB.Value = True Then
CCB = "CCB"
Else: CCB = ""
End If

If ChbSF.Value = True Then
SF = "SF"
Else: SF = ""
End If

If ChbCentre.Value = True Then
CENTRE = "Centre"
Else: CENTRE = ""
End If

If ChbFM.Value = True Then
FM = "FM"
Else: FM = ""
End If

If ChbGCH.Value = True Then
GCH = "GCH"
Else: GCH = ""
End If

Dim myNames() As Variant
myNames = Array(CBFM, CCB, SF, CENTRE, FM, GCH)

If OptOpenFile.Value = True Then
MsgBox "This will not refresh files automatically"
For i = LBound(myNames) To UBound(myNames)
If i = "" Then
Else
Workbooks.Open Filename:=mypath & myNames(i) & ".xls"
End If
Next i
Else
End If

If OptSaveValued.Value = True Then
MsgBox "This will save a refresh files and save a valued copy."
Dim mysheets As Long
Dim r As Long
mysheets = ActiveWorkbook.Worksheets.Count
Dim when
when = Now
For i = LBound(myNames) To UBound(myNames)
Workbooks.Open Filename:=mypath & myNames(i) & when & ".xls"
For r = 1 To mysheets
With ActiveWorkbook.Worksheets(r)
.Range("F17").FormulaR1C1 = "X"
.Range("F17").FormulaR1C1 = "TGBP"
.Application.Run "ResendF9"
Calculate
Khalix
Calculate
End With
Next r
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=mypath & "valued\" & myNames(i) & ".xls"
ActiveWorkbook.Close
Next i
Else
End If

End Sub

 

[tmischler]

ah = needed to put:

if myNames(i) = "" then

instead of

if i = "" then

thanks for the help

 

[tusharm]

I don't know if this is meant as a question or a solution, but in any case, please format the code for readability. Use the 'Code' button to put a pair of start-code and end-code tags around the code.

Ah - I think it's because i is a count of the number of items in the array. Below is the whole code:

Private Sub CmdOK_Click()

Dim mypath As String
Dim i As Long
{snip}