Menu update

[donh]

Hi there,

I've had a go and created a couple of additions to the menu bar using
VBA. Some of the captions are based on a cell ref which contains a
variable date in text form. I need to call my menu procedure when the
cell contents that I have refrenced to change (a new date is entered),
but I don't know how.

Hope you can help

DonH

 

[Bob Phillips]

You would use worksheet change event code, but you would need the name of
the menu and the sub-menu item to change it. Something like


Const WS_RANGE As String = "H10" '<== change to suit

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Application.CommandBars("myCB").Controls("myButton").Caption =
..Value
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click


--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

 

[Bob Phillips]

Missed the first line of the code

Private Sub Worksheet_Change(ByVal Target As Range)

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

 

[donh]

Many thanks Bob, not sure I understand but i will give it a go.

DonH

 

[donh]

Bob,

Had a look and didn't understand. Have copied part of my VBA below.
Can you explain a little more please.

Many thanks

DonH

Month1 = Worksheets("Setup").Range("I5")
MyYear1 = (Month1)

Set MenuItem = NewMenu2.Controls.Add _
(Type:=msoControlButton)
With MenuItem
..Caption = MyYear1
..OnAction = "Period1"
..FaceId = 2114
End With

 

[Bob Phillips]

This is problematical, as you will have no idea what the menu caption is at
any time.

Presuming that your menu is recreated every time you open the workbook, make
MenuItem a global variable, and then use that in the code


Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H10" '<== change to suit

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
MenuItem.Caption = .Value
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click


--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)