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

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
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

M
  • Question Question
Prevent worksheet change from triggering when similar macro is executed?
Replies
1
Views
163
AlphaFrog
AlphaFrog
B
  • Solved
Event code to give message box and change color of auto-shape
Replies
4
Views
790
bh24524
B
M
  • Question Question
Excel Time Keeping "Application"
Replies
0
Views
582
Mrock1
M
R
  • Question Question
VBA - Copy/Move value from cell A1 to A2 when cell A1 is changed.
Replies
2
Views
1K
Rob5060
R
A
  • Solved
Calendar form cancel button passing data to worksheet
Replies
7
Views
1K
AndyEd
A

Forum statistics

Threads
1,225,477
Messages
6,185,216
Members
453,283
Latest member
Shortm88

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