Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell name to change name in codes of command buttons
The hardest thing about asking for help is to express the problem so that others can understand it - the issue is so clear in OUR minds that it's hard to understand how someone else can't grasp it immediately - and that's not counting the difference in the way folks think. At any rate, after reading and re-reading your note, it seems to me that you want to have a _set_ of CommandButtons on your Index worksheet, and you want the captions of those buttons and the sheets to which clicking one of the buttons will take you to be specified in a cell in the A5:E9 range. If that's the case, then assuming that CommandButton1 is mapped/linked/paired with the contents of A5 then the following _Click event code would work to take you to cell A1 of whatever sheet is named in A5: Private Sub CommandButton1_Click() Dim sSheet as string sSheet = Worksheets("Index").Range("A5").Value Worksheets(sSheet).Select Application.GoTo Worksheets(sSheet).Range("A1"), Scroll:=True 'or false?? End Sub and you would need similar code (but referencing a different cell on the Index worksheet, of course) for each of your CommandButtons. As to updating the command button captions when the sheet names in A5:E9 change, you're pretty much on track with your code snippet - except that it appears that Set myRngToCheck = Me.Range("a5,a6 ") should be Set myRngToCheck = Me.Range("a5:e9") to get the entire range of cells that the sheet names are in. Also, it's not clear in the step where you actually update the button caption - Worksheets("Index").CommandButton1.Caption = Format(Target.Value, "mmmm dd, yyyy") you are trying to display the sheet name in a datetime format. It seems that a simple Worksheets("Index").CommandButton1.Caption = Target.Text would be what you needed. The above changes should get you closer. Now, for an out-of-the-box idea - you could implement this with one command button, if you ask your users to select which of the sheets they want to go to. In other words, have the A5:E9 range visible, instruct the user to select one of the cells and then click on a single button. To do that, the _Click event code would be modified to: Private Sub CommandButton1_Click() Dim sSheet as String Dim sCell as String Dim Target as Range Dim myRngToCheck As Range Set Target = Selection Set myRngToCheck = Me.Range(A5:E9) 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 sCell = Target.Address(0,0) sSheet = Target.Text Worksheets(sSheet).Select Application.GoTo Worksheets(sSheet).Range(sCell), Scroll:=True 'or false?? End Sub Of course, it may not fit what you are doing to make your user go back to a single range to make the selection, but if it would work, it would save you a lot of typing! -- jamescox ------------------------------------------------------------------------ jamescox's Profile: http://www.thecodecage.com/forumz/member.php?userid=449 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=112399 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cell name to change name in codes of command buttons | Excel Discussion (Misc queries) | |||
trying to hide command buttons in userform if cell empty | Excel Programming | |||
VB codes for option buttons | Excel Discussion (Misc queries) | |||
Using command buttons change the value in a form text box. | Excel Programming | |||
COMMAND BUTTONS and CELL COLOR | Excel Discussion (Misc queries) |