[x]
Posted via EE Mobile

Search, ask, and monitor your questions on the go with EE Mobile. Visit Experts Exchange from your mobile device and never be out of touch again.
Question
[x]
Attachment Details
[x]
The Solution Rating System

With so many solutions, how can you tell which solutions are most likely to help you and which ones are not? To provide you with a tool to use, we rate our solutions based on various elements that most accurately determine if a solution is a quality solution. To explain what factors affect the solution rating, here are the elements we take into consideration when formulating our solution rating.

  • The Grade of the Solution
  • The Zone Rank of the Expert Providing the Solution
  • The Number of Author and Expert Comments
  • The Number of Experts Contributing
  • The Feedback of the Community

Your Input Matters
Because of the way the system is set up, the most important variable in this equation is you. As a member of Experts Exchange, you are able to cast your vote on the quality of the solutions in regard to how complete, accurate, helpful and easy to understand each solution is. When you provide your feedback, each rating is adjusted accordingly. So, if you see a solution that has a poor rating that you think is a good solution, let us know by rating it. As you do, the rating will be adjusted and will become more accurate for other members of our site.

If you have any suggestions that you would like to make for our rating system, please ask a question in the Suggestions Zone of Community Support.

Thank you!
9.9

Use ActiveX Checkbox name and Value in Subroutine (MS Excel 2007)

Asked by Acheron32 in Microsoft Excel Spreadsheet Software, ActiveX, Automation

Tags: Excel 2007, ActiveX, Checkbox, subroutine, shape, value

I'm trying to reference the name of a checkbox within its own subroutine. I've tried to use the Application.Caller method, but, as these are ActiveX controls, this doesn't work. My workbook contains a lot of these checkboxes so it isn't practical to change them all. Their behavior is identical though, so I was hoping to have a single subroutine all checkboxes would utilize.
So, I have attempted to create a class module to make this work. I am able to pull the name of the checkbox, but now I am unable to get its value (True or False depending on whether it is checked or not), as this is supposed to signal one of two different subroutines (Copy data or Remove data). Any ideas how I can get both bits of information? I'm fairly new to VBA, so please excuse my ignorance! Most of what I know is from scouring forums. 
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:

'Code on Worksheet
Option Explicit 
 
Dim mcolEvents As Collection 
 
Private Sub Worksheet_Activate() 
    Dim cCBEvents As clsWSCtls 
    Dim shp As Shape 
     
    Set mcolEvents = New Collection 
     
    For Each shp In Me.Shapes 
        If shp.Type = msoOLEControlObject Then 
            If TypeOf shp.OLEFormat.Object.Object Is MSForms.CheckBox Then 
                Set cCBEvents = New clsWSCtls 
                cCBEvents. Name = shp.Name 
'Error is here as the Shape property doesn't have a value
'But is recognize if I use it in a standard subroutine
                cCBEvents. Value = shp.Value
                Set cCBEvents.mCheckboxes = shp.OLEFormat.Object.Object 
                mcolEvents.Add cCBEvents 
            End If 
        End If 
    Next 
    
End Sub 
'''''''''''''''''
'Code in Class Module
Option Explicit 
 
Private mName As String 
 
Public WithEvents mCheckboxes As MSForms.CheckBox 
 
Public Property Let Name(pzname As String) 
    mName = pzname 
End Property 
 
Public Property Let Value(pzvalue As String)
    mValue = pzvalue
End Property
Private Sub mCheckboxes_Click() 
    Dim iCb As Long 
    If mName.mValue = True Then
    Call Module3.DataCopy
    End If
    If mName.mValue = False Then
    Call Module3.DataRemove
End Sub
[+][-]11/09/09 04:24 AM, ID: 25775215Accepted Solution

View this solution now by starting your 30-day free trial. Setting up your free trial is quick, easy, and secure. We will return you to this solution, unlocked, when you're done.

About this solution

Zones: Microsoft Excel Spreadsheet Software, ActiveX, Automation
Tags: Excel 2007, ActiveX, Checkbox, subroutine, shape, value
Sign Up Now!
Solution Provided By: rorya
Participating Experts: 1
Solution Grade: A
 
 
Loading Advertisement...
20091118-EE-VQP-93 - Hierarchy / EE_QW_3_20080625