Save Button name in a variable

[Hazelwood]

In my procedure I save the value of a lookup data item as the name of a button.

ie.
member = Worksheets("Current Members").Cells(c, 1)


<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #000000 ; background-color: #ffffff}span.s1 {color: #011993}</style>With btn
.OnAction = "A02_AddAMember"
.Caption = member
.Name = "Btn" & 8
End With

Now I want to save the .caption value to another variable in another sub

ie. member2 = .caption value

But I cannot figure out how to do this.

Can anyone help me out?

 

[dmt32]

Hi,
pass the variable to your called procedure as an argument


example

Your procedure

      Dim Member As String
    
    Member = Worksheets("Current Members").Cells(c, 1)
    
    With btn
    .OnAction = "A02_AddAMember"
    .Caption = Member
    .Name = "Btn" & 8
    End With
    
    MyCalledProcedure Member

the procedure you are calling

Sub MyCalledProcedure(ByVal Caption As String)


        MsgBox Caption


End Sub

Hope Helpful


Dave

 

[Hazelwood]

Thank you Dave ... your process worked

 

[dmt32]

Thank you Dave ... your process worked

Most welcome - many thanks for feedback

Dave

 

[Hazelwood]

Dave,

Upon review, I'm still stuck.

The above solution had the value in member already and we passed it to MyCalledProcedure member. I cannot save "A02_AddAMember" with a "(variable name)" as the .OnAction value (I tried to use Caption).

So I'm back to square one, how can a Button pass on the value of the .caption field when the .OnAction calls the sub?

 

[dmt32]

Hi,
post code you are having issue with.

Dave

 

[Hazelwood]

[TABLE="width: 1095"]
<colgroup><col></colgroup><tbody>[TR]
[TD] For c = 2 To LastMemberRow[/TD]
[/TR]
[TR]
[TD] If memberleft5 = Left(Worksheets("Current Members").Cells(c, 1), 5) Then[/TD]
[/TR]
[TR]
[TD] i = i + 1[/TD]
[/TR]
[TR]
[TD] Select Case i[/TD]
[/TR]
[TR]
[TD] Case 1[/TD]
[/TR]
[TR]
[TD] member1 = Worksheets("Current Members").Cells(c, 1)[/TD]
[/TR]
[TR]
[TD] Set t = ActiveSheet.Range(Cells(x + 10, y + 1), Cells(x + 11, y + 5))[/TD]
[/TR]
[TR]
[TD] Set btn = ActiveSheet.Buttons.Add(t.Left, t.Top, t.Width, t.Height)[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD] With btn[/TD]
[/TR]
[TR]
[TD] .OnAction = "A03_UpdateAMember" & i[/TD]
[/TR]
[TR]
[TD] .Caption = Worksheets("Current Members").Cells(c, 1).Value[/TD]
[/TR]
[TR]
[TD] .Name = "Btn" & 1[/TD]
[/TR]
[TR]
[TD] End With[/TD]
[/TR]
[TR]
[TD] Case 2[/TD]
[/TR]
[TR]
[TD] member2 = Worksheets("Current Members").Cells(c, 1)[/TD]
[/TR]
[TR]
[TD] Set t = ActiveSheet.Range(Cells(x + 13, y + 1), Cells(x + 14, y + 5))[/TD]
[/TR]
[TR]
[TD] Set btn = ActiveSheet.Buttons.Add(t.Left, t.Top, t.Width, t.Height)[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD] With btn[/TD]
[/TR]
[TR]
[TD] .OnAction = "A03_UpdateAMember" & i[/TD]
[/TR]
[TR]
[TD] .Caption = Worksheets("Current Members").Cells(c, 1).Value[/TD]
[/TR]
[TR]
[TD] .Name = "Btn" & 2[/TD]
[/TR]
[TR]
[TD] End With

etc ...

With the buttons on the worksheet, I will click on a button.

It will call the sub "A03_UpdateAMember" & i

I now need the value of .Caption as it has the lookup value I need to continue

[TABLE="width: 1095"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Public Sub A03_UpdateAMember1()[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]'[/TD]
[/TR]
[TR]
[TD] member = .Caption (put this here to show what data value I want)[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD] ' the below sub will use member as the lookup value

[/TD]
[/TR]
[TR]
[TD] Call A03c_PullCurrentMemberInfo[/TD]
[/TR]
[TR]
[TD] Call A03d_PullPaymentInfo[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]End Sub[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Thank you for following up

 

[dmt32]

Hi,
You code is not fully clear but I am take it from the thread conversation that you want to pass the Button Caption to the procedure assigned to OnAction?

It has been awhile since I have used OnAction property but I think your will need to be modified as follows:

In your main procedure:

  With btn
        .Caption = Worksheets("Current Members").Cells(c, 1).Value
        .OnAction = "'A03_UpdateAMember" & i & """" & .Caption & """'"
        .Name = "Btn" & 2
    End With

note the change of Order in code structure where I have applied the Caption property FIRST as it is required in the OnAction property to pass to the assigned procedure.



In the procedure you are calling you will need to add a parameter to pass the caption to

Public Sub A03_UpdateAMember1(ByVal Caption As String)


MsgBox Caption
'
'member = .Caption (put this here to show what data value I want)
' the below sub will use member as the lookup value
Call A03c_PullCurrentMemberInfo
Call A03d_PullPaymentInfo
End Sub

Hope Helpful

Dave