×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Checkbox Trickery...

Checkbox Trickery...

Checkbox Trickery...

(OP)
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") 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

RE: Checkbox Trickery...

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").CheckBoxes
        If box.Value = "1" Then Cells(1, 1).Value = box.Name
    Next

End Sub

RE: Checkbox Trickery...

(OP)
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").Range("A16:D34").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

RE: Checkbox Trickery...

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","")".

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

RE: Checkbox Trickery...

(OP)
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
 

RE: Checkbox Trickery...

(OP)
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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources