Changing a procedures code from within another procedure

L

litrelord

Well-known Member
Joined
Dec 1, 2002
Messages
519
Hmm, couldn't think of a more descriptive subject line I'm afraid. Brain must be lagging a bit today!

What I was hoping to do was change the code in the Worksheet_SelectionChange section of a sheet when the user runs a macro. Or, more accurately, when the user presses a toolbar button.

Is there a way I can do this quite simply or would I need to look further into class modules which I haven't started on yet?

Thanks for your time

Nick
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
Sort by date Sort by votes
That's tricky.

It would be easier to set a Public variable in the code for your toolbar button, then test its value and act accordingly in your Worksheet_SelectionChange event procedure.
 
Upvote 0
Andrew,

sounds like a better idea to me. I can set the variable OK but I don't know how to assign a toolbar button with a value. Whenever I add toolbar buttons they are buttons which you press and release if you see what I mean. Rather than buttons which are either on/off.

How would I go about adding this?

Thanks
 
Upvote 0
You can use the State property. This codes adds a CommandBar with one Button. Clicking the Button runs the MyMaco procedure which toggles the State property.

Code: 
Sub AddBar()
    Dim Bar As CommandBar
    Dim Btn As CommandBarButton
    Set Bar = CommandBars.Add
    With Bar
        .Name = "MyBar"
        .Visible = True
    End With
    Set Btn = Bar.Controls.Add(Type:=msoControlButton)
    With Btn
        .FaceId = 283
        .OnAction = "MyMacro"
        .Caption = "Test"
    End With
End Sub

Sub MyMacro()
    Dim Bar As CommandBar
    Dim Btn As CommandBarButton
    Set Bar = CommandBars("MyBar")
    Set Btn = Bar.Controls("Test")
    If Btn.State = msoButtonDown Then
        Btn.State = msoButtonUp
    Else
        Btn.State = msoButtonDown
    End If
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Question
Event code to give message box and change color of auto-shape
Replies
4
Views
157
bh24524
B
R
  • Question
VBA - Copy/Move value from cell A1 to A2 when cell A1 is changed.
Replies
2
Views
587
Rob5060
R
A
  • Solved
Calendar form cancel button passing data to worksheet
Replies
7
Views
398
AndyEd
A
A
  • Solved
Calendar issue
Replies
1
Views
1K
AndyEd
A
C
  • Question
Help with code: input boxes for filepath and end to file name
Replies
9
Views
817
Micron
M

Forum statistics

Threads
1,214,943
Messages
6,122,380
Members
449,080
Latest member
Armadillos

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top