New Excel Forum

This forum has been moved to TeachExcel.com

Ask all future questions in the New Excel Forum.

ExcelKey

help with clearing checkboxes from userform2

Macros, VBA, Excel Automation, etc.

help with clearing checkboxes from userform2

Postby laurensims41 » Tue Sep 13, 2016 11:21 am

Userform 2 has 32 checkboxes. The numbers for the value change with each cell ; i.e. "event.sensor[4].cc[2].enable= ON" . 1 -32 go all the way A1 to A32. The checkbox values are associated with sheet7. Anyway to simplify this code? Attached is workbook

The other problem i am having is setting all the checkboxes back to default.
I need that to be activated on an exit command button from a different user form.
When i add my code to the exit command to default checkbox values to unchecked it doesnt change the value back to off on sheet 7.

I have attached the workbook
  • 0

You do not have the required permissions to view the files attached to this post.
laurensims41
Rookie
 
Posts: 15
Joined: Feb 3, 2016
Reputation: 0
Excel Version: Office Professional Plust 2010

Re: help with clearing checkboxes from userform2

Postby NoSparks » Tue Sep 13, 2016 3:49 pm

Userform2 has 36 check boxes.
The first 32 are to have sheet7 A1-32 "toggle ON-OFF", right ?

27 and 31 are both labeled Low Temp

The others are:
Microwave Major
RRH Intrusion Alarm
RRH Humidity Alarm
RRH Power Failure

Do these need addressed ?
  • 0

NoSparks
Excel Hobbyist
 
Posts: 637
Joined: May 28, 2014
Reputation: 103
Excel Version: 2010

Re: help with clearing checkboxes from userform2

Postby laurensims41 » Tue Sep 13, 2016 3:59 pm

Copy and Paste error.
There should be no duplicates my fault.
I fixed it in the new attachment.
Low Temp
AC Power Failure
Microwave Major
Microwave Minor
RRH Power Failure
RRH Humidity Alarm
RRH Intrusion Alarm
  • 0

You do not have the required permissions to view the files attached to this post.
laurensims41
Rookie
 
Posts: 15
Joined: Feb 3, 2016
Reputation: 0
Excel Version: Office Professional Plust 2010

Re: help with clearing checkboxes from userform2

Postby NoSparks » Tue Sep 13, 2016 7:07 pm

Give this a try.

I had to change a few things slightly to accommodate the resetting of the Environmentals.
The Clear button code has a couple things commented out in order to execute in this file.
I'm assuming this is from another form that actually has these items.

Copy and paste between user forms is like Russian Roulette when controls use the default names as given in the VBA, and all of these check boxes use the default Checkbox# for their name, from 1 to 32.
The rows on sheet 7 are matched to the check box #.

Make sure things work as desired in this workbook before trying to implement in another file, thanks

Good Luck
  • 0

You do not have the required permissions to view the files attached to this post.
NoSparks
Excel Hobbyist
 
Posts: 637
Joined: May 28, 2014
Reputation: 103
Excel Version: 2010

Re: help with clearing checkboxes from userform2

Postby laurensims41 » Thu Sep 15, 2016 9:41 am

So you suggest changing all the checkbox names to an actual name. i.e instead of CheckBox1_Click() to BattDis_Click()

Code: Select all
Dim i As Integer
    Dim ctrl As MSForms.Control
Sheet7.Activate
For Each ctrl In UserForm2.Controls
    If TypeName(ctrl) = "CheckBox" Then
        If Mid(ctrl.Name, 9) < 33 Then
            i = CLng(Mid(ctrl.Name, 9))
            With Sheet7
                If ctrl.Value = True Then
                    .Cells(i, 1).Value = Left(.Cells(i, 1), InStr(.Cells(i, 1), "=")) & "ON"
                Else
                    .Cells(i, 1).Value = Left(.Cells(i, 1), InStr(.Cells(i, 1), "=")) & "OFF"
                End If
            End With
        End If
    End If
Next ctrl

Is there anyway you can help me understand this part of the code a little more. I have never worked with Mid, Instr and Left before.In this part of the code what is 9 referring too? and the 1?

I appreciate your help on this I have learned so much.
  • 0

laurensims41
Rookie
 
Posts: 15
Joined: Feb 3, 2016
Reputation: 0
Excel Version: Office Professional Plust 2010

Re: help with clearing checkboxes from userform2

Postby NoSparks » Thu Sep 15, 2016 11:32 am

So you suggest changing all the checkbox names to an actual name. i.e instead of CheckBox1_Click() to BattDis_Click()

Not necessarily, if they are OK the way they are leave them alone. Hind site is 20-20. Naming them as they are created is much easier than trying to change them after the fact.

They would still have to be consistently named. If you were to copy and paste a CheckBox1 to another form that already has check boxes (that are also default names) then this check box would be automatically renamed to CheckBox next number for that form. I'd probably change the names to CkBox1, CkBox2, etc. so Excel doesn't change the names.
That would also require renaming all the CheckBoxX_Click procedures and changing the part of the macro that gets the check box number from the name. (which is the next explanation)

Is there anyway you can help me understand this part of the code a little more. I have never worked with Mid, Instr and Left before.In this part of the code what is 9 referring too? and the 1?

The 1 in .cells(i, 1) is the column number, could have used "A"
Mid and 9 in Mid(ctrl.Name, 9). This is extracting the number part of the control name.
Mid gets the middle characters starting at the 9th character. Because there is no argument for how many to get it takes them all from the 9th to the end.
Left and Instr are used to extract the left portion of the cell value up to the equal sign.
Instr gets the position of the equal sign and Left uses that as the number of characters to extract.

The value of a checkbox is True or False. Based on that we are adding ON or OFF after the equal sign in the cell that has the same row number as the check box number. We are cycling through all the checkboxes and writing each cell every time.

Wow... not sure I can make sense of that, with a little luck maybe you can decipher it.

NoSparks
  • 0

NoSparks
Excel Hobbyist
 
Posts: 637
Joined: May 28, 2014
Reputation: 103
Excel Version: 2010


Return to Macros and VBA Questions

Who is online

Users browsing this forum: No registered users and 4 guests