Can a shared macro determine its calling object? 1

[MrRedwood]

I have several dozen forms (checkboxes) that I hope can share a macro, since a bit more programming on one would be better than maintaining identical code for each.

Scenario: for each question on an audit form, there might be a yes/no checkbox or a numerical spinner -- but there is always an associated "Not Applicable" checkbox. The latter, if checked, should disable the former as well as affect the underlying calculations.

So my idea is use the name of the calling checkbox (the enable/disable one) to re-create the name of the affected checkbox (or spinner) for editing, with some trivial text operations.

So what I need is something like "thisCheckbox.Name".

I couldn't use ActiveX controls since they can't share events, but stumbling through the online help and my general purpose Office references don't clue me in to this.

Any help?
--
Richard

 

[fumei]

Not quite following.

First off, what application is this?

Second, these are different on different userforms? And they are all displayed at the same time?

I do not understand what you mean by "re-create the name". Each checkbox HAS a name. So what is being "re-created".

Perhaps if you walked it through step-by-step.

Gerry

 

[MrRedwood]

OK, more details:

This is in Excel. I believe I was once able to do the equivalent in Word using "Selection", but my experiments in Excel seem to indicate that this won't work. Oh, Excel 2002, I believe.

The checkboxes (etc) are directly placed on a single worksheet, since the user wants to be able to see the effect of the calculations that result from these controls. Is there some specific reason for creating a userform other than gather controls together in a single place? Because in this case, the current objective is a worksheet that simply has controls overlaying some cells to simplify user input.

Each worksheet in the workbook after the first is a single audit record/input-form. The whole project should have been done in Access or some other database, but the powers that be have decided that my user's department shouldn't have access to Access. (They also forbid Firefox and mandate IE because they are too busy with things like security to permit such user flexibility).

What I mean by recreate: say the user checks the box with the name "CB_Form6520_needed", which controls whether or a certain form was necessary. When that box is checked, a neighboring box named "CB_Form620_used" is cleared and disabled; when the "_needed" box is cleared, the "_used" box is enabled again. This relationship holds for about two dozen pairs; another dozen or so similar pairings are between numerical spinners and their enablers.

I'd like all the checkbox pairs to share macros, since the only difference is the names. So when the "_click" macro for "CB_Form6520_needed" is checked, the macro should strip the "_needed" off the end of its Name and append "_used" in order to create the name of the target.

Since all the "_needed" checkboxes will share a single "_click" macro, that macro needs some way of figuring out which checkbox was just clicked. I tried "me.Name", but Excel didn't like that one.

 

[fumei]

Nope. Me is for userforms, not controls from the Forms toolbar. Confusing isn't it.

Unfortunately, while each control form DOES have its own name, it is not a property that is exposed. I do not know of a way that, say Checkbox3_Click() can determine it is "Checkbox3" that has been clicked.

However, there are far more knowledgeable people here. Hopefully someone can help.

Gerry

 

[taupirho]

This can be done quite readily using the oleobjects collection.

Assume you have say 4 checkboxes on your spreadsheet called CheckBox1,2,3,4 and a button.

Add this code to the button click event and it will cycle thro all the checkboxes, message out their name AND check them.

For Each Olo In OLEObjects
if Olo.progId = "Forms.CheckBox.1" then
msgbox(olo.name)
Olbject.Value = True
end if
Next

 

[MrRedwood]

I don't see how that helps. The problem isn't finding names of any arbitrary check box, but the specific name of the one that has just been checked.

If, for example, the "gotfocus" event were not an event, but a property, then I could cycle through the checkboxes and see which one had the focus. But since it is an event, that just redefines the problem.

Again: the crucial problem is that a single macro is *shared* but a number of checkboxes as their click event. But that event must then determine which of those checkboxes has trigger it in order to know what to do.

[Frustratingly, "me" doesn't seem to be documented in any useful way, I don't remember where I stumbled on it, and neither my online help nor Microsoft's web help responds to queries about "me".]

 

[rmikesmith]

Richard,

I am putting together some example code to do what you want (although there may be some tweaking involved). Will post the code and instructions when I've got it working.


Regards,
Mike

 

[fumei]

I too am thinking there could be a way. Working on it, but am interested in what Mike comes up with.

You are certainly correct in that taupirho's code does not help. I had thought of that as well, but it does not help. As I stated, each control has a name, and yes you can cycle through and DO something, but there is no method for determining its own firing.

But....I think there may be a way to dance around through this.

Gerry

 

[rmikesmith]

Richard,

Here is a strategy that I believe will work for you. To follow along and try this out, create a separate workbook. On the displayed worksheet create a few CheckBox pairs (side-by-side), which will simulate the questions having a 'Yes/No' CheckBox as well as the 'NA' CheckBox. It is critical that you use the ActiveX version; i.e., use the CheckBox control from the Control Toobox toolbar. I created three pair (6 total). For the first pair, rename the 'Yes/No' CheckBox to be CheckBox1A (if this was the first one added to the worksheet, it will likely be named CheckBox1 by default). Rename the 'NA' checkbox to CheckBox1B. In similar fasion rename the other two pairs of checkboxes. When finished the checkbox names should be as follows:
Yes/No Checkboxes NA Checkboxes
CheckBox1A CheckBox1B
CheckBox2A CheckBox2B
CheckBox3A CheckBox3B

For each of the 'NA' Checkboxes, open the Properties dialog and change the GroupName property to NA. You may also wish to change the Captions to "Yes/No" and "NA", respectively to more easily keep track. Now deactivate "design mode" (from the Control Toolbox toolbar).

Switch to the VBE and insert a standard code Module and a Class Module. Rename the Class Module CCheckBoxes and copy the following code into it:

Public WithEvents CheckBoxGroup As MSForms.CheckBox
Private m_ID As Integer


Private Sub CheckBoxGroup_Click()
Dim Pos As Integer
Dim i As Integer
  
   ActiveSheet.OLEObjects("CheckBox" & ControlID & "A").Object.Enabled = _
   Not CheckBoxGroup.Value
End Sub


Public Property Get ControlID() As Integer
  ControlID = m_ID
End Property


Public Property Let ControlID(ByVal ID As Integer)
   m_ID = ID
End Property

In the standard Code Module, add this code:

Dim CheckBoxes() As New CCheckBoxes


Sub InitializeCheckBoxes()

Dim CheckBoxCount As Integer
Dim Ctl As OLEObject


   CheckBoxCount = 0
   For Each Ctl In ActiveSheet.OLEObjects
     With Ctl
       If TypeName(.Object) = "CheckBox" Then
         If .Object.GroupName = "NA" Then
           CheckBoxCount = CheckBoxCount + 1
           ReDim Preserve CheckBoxes(1 To CheckBoxCount)
           Set CheckBoxes(CheckBoxCount).CheckBoxGroup = .Object
           CheckBoxes(CheckBoxCount).ControlID = ExtractControlNumber(.Name)
         End If
       End If
     End With
   Next Ctl

End Sub


Function ExtractControlNumber(ByVal CtlName As String) As Integer
Dim i As Integer
Dim NumStr As String
Dim OneChar As String

   NumStr = ""
   For i = 1 To Len(CtlName)
     OneChar = Mid$(CtlName, i, 1)
     If OneChar Like "[0-9]" Then
       NumStr = NumStr & OneChar
     End If
   Next i
   ExtractControlNumber = CInt(NumStr)
   
End Function

Save the workbook then run the InitializeCheckBoxes procedure. Now test it by checking/unchecking the 'NA' checkboxes. The corresponding 'Yes/No' checkboxes should be enabled or disabled appropriately.

After you've tried this post back. There are modifications that will need to be made to handle your specifics; namely, multiple worksheets.

Note: The basic idea (using a Class Module + Array) is from John Walkenbach's Spreadsheet Page website. The specific url is

http://www.j-alk.com/ss/excel/tips/tip44.htm

Regards,
Mike

 

[TonyJollans]

What I think you are asking for is this.

If you assign a macro to a checkbox from the Forms Toolbar, you can use [blue][tt]Application.Caller[/tt][/blue] to get the name of the control which invoked the macro.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[rmikesmith]

Blast you, Tony Jollans!

That's what I was looking for originally, but couldn't recall the applicable Object.Property


Regards,
Mike

 

[MrRedwood]

Mike - thanks for the huge amount of work. I'm gonna keep studying that code and try to learn something, but...

As you recognized, Tony nailed it.

However: this was tough enough that I hope one of you two could write a nice FAQ for future users? It certainly simplifies writing macros for large numbers of controls!

Thanks, both...
--
Richard