Variable name for VBA Command Button

  • Thread starter Thread starter donh
  • Start date Start date
D

donh

Hi,

Thought I had finished, but a user of my spreadsheet has accidently
removed a hyperlink from the Index of the project I've been working on.
To ensure this doesn't happen again I'm swapping cell based hyperlinks
for VBA Command Buttons. These have been fine but the last few are
based on variable data that appears in cells when the sheet is setup.
for example Sheet 1 Cell A1 contains Jan-Feb cell A2 contains Mar-Apr
etc. The contents of these cells change each year, so I guess I need a
formula in the caption to point at these cells but don't know the
formatt it should take.

Any help would be gratefully received

DonH
 
Don,

You could do it with event code that traps a change in cells A1:A4, and
changes the appropriate button accordingly

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

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Select Case .Address(False, False)
Case "A1": Me.Buttons("Button 1").Caption = .Value
Case "A2": Me.Buttons("Button 2").Caption = .Value
Case "A3": Me.Buttons("Button 3").Caption = .Value
Case "A4": Me.Buttons("Button 4").Caption = .Value
End Select
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


This assumes that the buttons are Forms buttons.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)
 
Bob,

I have added your code to the VBA page where my Index is. So far no
luck in getting any further. My buttons were/are VBA Command Buttons
not Form Buttons but have tried creating Form buttons and renaming
those Button 1 etc and then updating cell A1, so far without any
change to the buttons name.

Could you please let me know where I might be going wrong. Also if
possible can this also be done with VBA command buttons. I wanted to
go the VBA route, if it worked, to protect the buttons from accidental
editing or deletion.

Many thanks

Don H
 
Bob,

Forget that. been playing some more and don't think I have done
anything different but that now works thank you. I also recognise my
comment on protection is wrong as I am able to protect the Form button
too.

However :-) if this can be done with VBA Command Buttons I would be
interested as this would help with the colour formatting to group my
index subjects
 
Here you go Don, control toolbox buttons

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

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Select Case .Address(False, False)
Case "A1": Me.OLEObjects("CommandButton1").Object.Caption =
..Value
Case "A2": Me.OLEObjects("CommandButton2").Object.Caption =
..Value
Case "A3": Me.OLEObjects("CommandButton3").Object.Caption =
..Value
Case "A4": Me.OLEObjects("CommandButton4").Object.Caption =
..Value
End Select
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)
 
Bob,

Thanks for being so quick. Sorry to say I still have a problem! I
found out what I had done differently to make it work, which is to
update the cell directly.

My worksheet is a works time sheet and has been designed to be used for
the next 10 years. The pattern of work is held in a table (as it
doesn't fall nicely into a monthly cycle).

A year start date is selected from a drop down, from there each period
date is set using a lookup to the work table. It is these period dates
I'm wishing to capture in the buttons. So no cell is actually typed
into. Starts with a drop down and completed by lookups.

Is there a way around this?


Many thanks

Don
 
Don,

Try this approach. It is less efficient as it happens every calculate, but
should work

Private Sub Worksheet_Calculate()

If Not IsError(Me.Range("A1").Value) Then _
If Not IsEmpty(Me.Range("A1").Value) Then _
Me.OLEObjects("CommandButton1").Object.Caption =
Me.Range("A1").Value
If Not IsError(Me.Range("A2").Value) Then _
If Not IsEmpty(Me.Range("A2").Value) Then _
Me.OLEObjects("CommandButton2").Object.Caption =
Me.Range("A2").Value
If Not IsError(Me.Range("A3").Value) Then _
If Not IsEmpty(Me.Range("A3").Value) Then _
Me.OLEObjects("CommandButton3").Object.Caption =
Me.Range("A3").Value
If Not IsError(Me.Range("A4").Value) Then _
If Not IsEmpty(Me.Range("A4").Value) Then _
Me.OLEObjects("CommandButton4").Object.Caption =
Me.Range("A4").Value

End Sub

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


donh said:
Bob,

Thanks for being so quick. Sorry to say I still have a problem! I
found out what I had done differently to make it work, which is to
update the cell directly.

My worksheet is a works time sheet and has been designed to be used for
the next 10 years. The pattern of work is held in a table (as it
doesn't fall nicely into a monthly cycle).

A year start date is selected from a drop down, from there each period
date is set using a lookup to the work table. It is these period dates
I'm wishing to capture in the buttons. So no cell is actually typed
into. Starts with a drop down and completed by lookups.

Is there a way around this?


Many thanks

Don
Click to expand...
 
Bob,

Bit confused, you mention in your post that it is less efficient
because it happens every calculate. Doesn't seem to!

Not that I mind. As this one is a Sub() I can execute I'll just have
an update button to run it when the year is set.

Thank you very much for your time and effort.

Don H
 
Bob,

I must confess I talk dribble. In future I shall make an effort to
check what I'm doing before replying to a post. Your suggestion seems
to work fine, haven't a clue why it didn't before.

As for being able to execute it, that at least for me, was dribble too.

If anyone would like to buy me a good book for Christmas, perhaps on
something else I'm more suited to, that would be nice.

I'm off to lie down in a dark room.

DonH
 
Back
Top