Checkbox Trickery... 1

[AussieAero]

Hi All, this is gonna be a little complicated to explain but it is a problem I have spent several weeks trying to solve with no luck.

I am trying to design an Excel workbook to import multiple sets of flight data from a text file containing rocket flight parameters recorded from hardware I have designed...

Now, a macro (in a worksheet "Summary&quot is used to import the text file into a new worksheet which it creates. It then creates a flight label in "Summary' and 2 checkboxes which are renamed according to the flight label. It does this for every new set of flight data I include.

To create the checkboxes I have simply copied another checkbox which exists on the sheet, pasted it at a cell I select, and rename it while it is already selected. Their purpose is to select which flight data set is to be graphed and printed for comparitive purposes.

All of this works properly and I have ensured that no invalid characters can be used when trying to auto lable the checkboxes.

THE PROBLEM:
Since each checkbox name is different, I am trying to find a way to determine the status of the checkboxes and select them, after reading the flight label however, it doesn't appear to like any stings used in the line.

AN EXAMPLE:
For the flight data labeled "flight2"

strAddFlightName = "Flight2" (This actually uses a cell value which changes as the cursor moves down the column)
strCheckbox = "chk" & strAddFlightName & "Graph"
If shtSummary.strCheckbox.Value = True Then
...

ATTEMPTED SOLUTIONS:
I have tried the above method as well as trying to use a control array to reference the boxes. This proved to be more diffucult as I was unable to name them as an array and, furthermore, the values will change in an array if a certain data set is removed. Since I cannot be guarenteed that the checkboxes are deleted (since I cannot select them) I cannot rely on the auto renumbering of the checkboxes anyhow...

That is it... If anyone out there can help me out I will be most appreciative.

Cheers

Steven

 

[ab123456]

I hope I have understood your question correctly.

You could try using the For each ... next command

for example, if you have a number of checkboxes on "Sheet1" and wish to display the names of the checkbox that are checked you could use something like

Sub test()
Dim box As CheckBox
Dim sheet As Worksheet

For Each box In Sheets("Sheet1&quot.CheckBoxes
If box.Value = "1" Then Cells(1, 1).Value = box.Name
Next

End Sub

 

[AussieAero]

Thanks ab

It seems you understood most of the problem. It looks like the code should work if it can be tweaked a little...

Firstly, where does the .Checkboxes method come from? I have never seen that nor can I get it to appear in my code. If I just use it then the sub seems to excecute without an error, however, the For Loop doesn't seem to work (with half the checkboxes checked and the other half unchecked). I tested this using the line MsgBox "Stuff" both inside and outside the For Loop and it did not work inside the loop.

The problem I still have is that there are more checkboxes on the sheet than the ones I am having issues with. Is it possible that if I get your example code working that it can be adapted to check for checkboxes within a certain cell range? For Example...

Sub test()
Dim box As CheckBox
Dim sheet As Worksheet

For Each box In Sheets("Sheet1&quot.Range("A1634&quot.CheckBoxes
If box.Value = "1" Then Cells(1, 1).Value = box.Name
Next

End Sub

Anyway, thanks for your help so far - you've given me a new spin to work with for a while

Cheers

Steven

 

[ab123456]

I was a bit confused when you said that the above code didnt pick up on your checkboxes so I had a bit of a play with excel and discovered there are two different ways excel does checkboxes.

In the standard toolbars list there are two toolbars that contain buttons for drawing checkboxes. If the button on the 'Control toolbox' toolbar is selected then the checkbox is treated as an embedded object and the above code does not work. If however the checkbox is drawn using the button on the 'Forms' toolbar the checkbox is treated as part of the sheet and the above code works.

I can only assume that your application contains embedded objects, this can be seen from the fact that when the box is selected the formula bar contains something like "=EMBED("Forms.CheckBox.1","&quot".

I am afraid I do not know a way of changing between the two types other than deleting all the checkboxes and redrawing them.

 

[AussieAero]

Thanks again ab

you were spot on with the 2 different checkboxes. It is interesting that they have done that. I tested the code with the different checkboxes and it works perfectly.

I will try it with my worksheet and see how it turns out.

Cheers

Steven

 

[AussieAero]

Hi again...

Well, I spent a bit of time messing around with your code, ab, and managed to get it working for the embedded checkboxes.

I still can't reference a specific checkbox from a generated string which is what I originally intended to do, however, by using your method I was able to select all of the checkboxes of a certain condition by naming the beginning of that group of checkboxes similarly.

I have included a copy of part of the code I have used it in for interests sake.

Private Sub chkGraphAll_Click()

Dim strThisFile As String
Dim strFlightName, strCheck As String
Dim intRow As Integer
Dim objName As Variant
Dim strObjName, strStartObjName, strEndObjName As String

strThisFile = ActiveWorkbook.Name
strCheck = shtSummary.chkGraphAll.Value

For Each objName In shtSummary.OLEObjects
strObjName = objName.Name
strStartObjName = Mid(strObjName, 1, 14)
strEndObjName = Mid(strObjName, 15)
If strStartObjName = "chkGraphFlight" Then
Select Case strCheck
Case True
shtSummary.OLEObjects(objName.Name).Object.Value = True
Case False
shtSummary.OLEObjects(objName.Name).Object.Value = False
End Select
End If
Next

End Sub

Thanks again for your help.

Steven