Menu update

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

donh - 28 Nov 2006 17:06 GMT

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

Reply to this Message

Bob Phillips - 28 Nov 2006 17:14 GMT

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)

> Hi there,
>
[quoted text clipped - 7 lines]
>
> DonH

Reply to this Message

Bob Phillips - 28 Nov 2006 17:22 GMT

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)

> 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
[quoted text clipped - 38 lines]
> >
> > DonH

Reply to this Message

donh - 28 Nov 2006 17:29 GMT

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

DonH

> Missed the first line of the code
>
[quoted text clipped - 50 lines]
> > >
> > > DonH

Reply to this Message

donh - 28 Nov 2006 17:43 GMT

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

> Missed the first line of the code
>
[quoted text clipped - 50 lines]
> > >
> > > DonH

Reply to this Message

Bob Phillips - 28 Nov 2006 20:34 GMT

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)

> Bob,
>
[quoted text clipped - 38 lines]
> > > If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
> > > With Target

Application.CommandBars("myCB").Controls("myButton").Caption =

> > > .Value
> > > End With
[quoted text clipped - 27 lines]
> > > >
> > > > DonH

Reply to this Message