Solved

form Controls change in excel

Posted on 2012-04-10
9
272 Views
Last Modified: 2012-04-16
Hello All,
I currently have all checkboxes in the worksheet that use auto filter to filter and copy data to another sheet. Everything is working all right. I just need to make a change. The checkboxes that refer to the M series like M1,M2,M3,M4,M5 needs to be in button mode, Not in checkboxes
.. and the Rest of the checkboxes remain the way they are. And the filtering happens  just the same
Now to change that in vba – for each controls in the worksheet, filter accordingly - can anyone help?
Attached file….

Thank you
R
somecheckboxes2ButtonsLoop.xlsm
0
Comment
Question by:Rayne
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
9 Comments
 
LVL 42

Accepted Solution

by:
dlmille earned 500 total points
ID: 37829699
Do you want command buttons or IN/OUT buttons (which work like checkboxes) for the M-Series?

With buttons, I assume you want them ON or OFF based on the push of the button.

we just create macros against each button like:

    [m123_Range].Columns(2).Cells(1).Value = IIf([m123_Range].Columns(2).Cells(1).Value = 1, 0, 1)
    Call doFilterGetResults
End Sub

and then comment out the test for the M1-M5 checkbox in the loop.

See attached.

With In/Out buttons, the behavior would be more like with checkboxes.  With push buttons, its hard to visibly show the "state" that the button push would be in.

Let me know if this is what you're looking for or if you want to try the IN/OUT buttons.

Dave
somecheckboxes2ButtonsLoop-r1.xlsm
0
 

Author Comment

by:Rayne
ID: 37829760
Hello Dave,

Thank you for replying :)
I need need command buttons for this.

R
0
 
LVL 42

Expert Comment

by:dlmille
ID: 37829767
Ok - that's what you got.  Check it out and see if the behavior is what you want.

Dave
0
Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

 

Author Comment

by:Rayne
ID: 37829800
Hello Dave,
If you can demo the push button, I can take a look – yes it would make more sense to see the state in which the section is..
Yes, that makes a lot of sense - With push buttons; it’s hard to visibly show the "state" that the button push would be in.
Users will first filter the data by M series and then sub filter them by the other checkboxes

R
0
 

Author Comment

by:Rayne
ID: 37829901
Thanks again Dave for your quick help. The command button works great. If you have time and this is less complex, then you may post the in / out button and I can take a look.
R
0
 
LVL 42

Expert Comment

by:dlmille
ID: 37830000
There's a couple ways to do this.  The push buttons are activeX controls (so caveats about using them in worksheets.  While I do, some folks have quirky resize problems, so most MVPs will tell you not to use them except in userforms).

Moving on...

1.  Could create a click event for each button toggled, and do work from there,
2.  Or, do it in the script we already have, and just check the state of the push button.

Since we have to have event code anyway when the toggle happens, I'm doing it via #1, above, so will be similar to the command button routine.

so in the MainViewSheet codepage we have code like this:

Private Sub TB_1_Click()
    [m123_Range].Columns(2).Cells(1).Value = IIf(TB_1.Value = -1, 1, 0)
    Call doFilterGetResults
End Sub

Open in new window


And for initialize we have added code like this:
Sub CBInitialize(wks As Worksheet)
Dim cBox As CheckBox
Dim objOLE As OLEObject

    'initialize all CheckBoxes to FALSE
    For Each cBox In wks.CheckBoxes
        If Left(UCase(cBox.Name), 2) = "CB" Then
            cBox.Value = False
        End If
    Next cBox

    'initialize all toggle buttons to FALSE
    For Each objOLE In wks.OLEObjects
        If objOLE.progID = "Forms.ToggleButton.1" And Left(objOLE.Name, 2) = "TB" Then
            objOLE.Object.Value = False
        End If
    
    Next objOLE
    
    Call clearMain(wks)
    
End Sub

Open in new window


We check the name as with check boxes, to ensure we don't change toggle buttons or check boxes we don't want to - there could be others we DON'T want to use with the filtering, which is why we named them in the first place ;)


PS - at some point, you'll want to know how to do an event or change based on a change to ANY control, without having to create a function/subroutine STUB for each control as we have done in this workbook.  When you're ready, ask that question as there are classes that can be developed that check for clicks all in one set of code.

Dave
somecheckboxes2ButtonsLoop-r2.xlsm
0
 

Author Comment

by:Rayne
ID: 37830250
Hello Dave,

That is great. This button looks way better than the other one and shows the state – that’s perfect. Yes, I will ask that question next for the event handling. Way less code than what I have got now :)


R
0
 
LVL 42

Expert Comment

by:dlmille
ID: 37849457
Here's a tip - instead of assigning a new macro to each checkbox (which in turn calls the same macro) just have each checkbox call the main macro that needs to be called.

Not sure why I didn't point that out, earlier.

Just have all the checkboxes that end up calling doFilterGetResults() just linked to doFilterGetResults() rather than a _Click macro that calls doFilterGetResults().

Cheers,

Dave
0
 

Author Comment

by:Rayne
ID: 37854528
Hello Dave,

I have posted two questions in EE

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Q_27678921.html
http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Q_27678898.html

When you have time, you may take a look. I have attached files in both cases.

Thank you :)

R
0

Featured Post

Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This article helps those who get the 0xc004d307 error when trying to rearm (reset the license) Office 2013 in a Virtual Desktop Infrastructure (VDI) and/or those trying to prep the master image for Microsoft Key Management (KMS) activation. (i.e.- C…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

717 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question