Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cell name to change name in codes of command buttons
Below are names in each cell
Column A b c d e Row 5 Sheet1 Sheet6 Sheet11 Sheet16 Sheet21 6 Sheet2 Sheet7 Sheet12 Sheet17 Sheet22 7 Sheet3 Sheet8 Sheet13 Sheet18 Sheet23 8 Sheet4 Sheet9 Sheet14 Sheet19 Sheet24 9 Sheet5 Sheet15 Sheet20 Sheet25 Sheet, tab, and CommandButton names are referenced by these cells name above, meaning if I change a name in any cell above it will change the sheet, tab and command button names referenced to the cell. E.G. A9 =sheet5, so CommandButton1 name = Sheet5, tab name = Sheet5 and in Sheet5, A1 = Sheet5 What I would like to do is: As each name in the cells above is referenced to a sheet and a Commandbutton, I would now like to be able to have the cells name above also change the codes Go To Worksheets, ("Index") to ("Sheet1") in the command buttons Go To Worksheets code below. Can this be done? Code for CommandButton to Go To sheet Private Sub CommandButton1_Click() Worksheets("Index").Select Application.GoTo Worksheets("Index").Range("A1"), Scroll:=True 'or false?? End Sub Code to Change CommandButton Name Private Sub Worksheet_Change(ByVal Target As Range) Dim myRngToCheck As Range Set myRngToCheck = Me.Range("a5,a6 ") If Target.Cells.Count 1 Then Exit Sub 'one cell at a time End If If Intersect(Target, myRngToCheck) Is Nothing Then Exit Sub End If Select Case Target.Address(0, 0) 'use uppercase addresses here and match the addresses above! Case Is = "A5" Worksheets("Index").CommandButton1.Caption _ = Format(Target.Value, "mmmm dd, yyyy") Case Is = "A6" Worksheets("Sheet2").CommandButton1.Caption _ = Format(Target.Value, "mmmm dd, yyyy") |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Command buttons | Excel Discussion (Misc queries) | |||
VB codes for option buttons | Excel Discussion (Misc queries) | |||
VB codes for option buttons | Excel Discussion (Misc queries) | |||
COMMAND BUTTONS and CELL COLOR | Excel Discussion (Misc queries) | |||
command buttons | Excel Worksheet Functions |