Array from Checkbox

tmischler

Well-known Member
Joined
Jun 17, 2004
Messages
669
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?
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

tmischler

Well-known Member
Joined
Jun 17, 2004
Messages
669
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

MrExcel MVP
Joined
May 28, 2002
Messages
11,029
So, why not include a IF statement that executes the Workbooks.Open only if entry i is not ""?
tmischler said:
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

Well-known Member
Joined
Jun 17, 2004
Messages
669
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

Well-known Member
Joined
Jun 17, 2004
Messages
669

ADVERTISEMENT

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

Well-known Member
Joined
Jun 17, 2004
Messages
669
ah = needed to put:

if myNames(i) = "" then

instead of

if i = "" then

thanks for the help
 

tusharm

MrExcel MVP
Joined
May 28, 2002
Messages
11,029
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.
tmischler said:
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}
 

Forum statistics

Threads
1,148,055
Messages
5,744,544
Members
423,882
Latest member
Seeham

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top