PDA

View Full Version : [SOLVED] Change multiple Form Control Button captions from a list on another worksheet



paspuggie48
03-19-2017, 05:39 AM
Dear Gurus

I have a workbook, the front page is "HOME" and the subsequent worksheets are named 001, 002, 003 etc. The ws names are listed on a separate ws called "ProjectNumbers" in ColA. The front ws "HOME" is where I have all the Form Control Buttons to jump to the respective worksheets. Each button is named the same as each ws in the wb and after an extensive search I found a lovely piece of code on this forum that assigns the respective macro to each one of them in relation to the macro list on "ProjectNumbers" in ColD.

Each Button caption is the same value as ColA (same as ws name) and ColD is the value for the macro to assign to each button.

This is what I copied and modified to suit my needs and it works PERFECTLY !


Sub AssignShapeMacros()
Dim ws As Worksheet
Dim wsH As Worksheet
Dim i As Integer
Dim oShp As Shape

Set ws = Worksheets("ProjectNumbers")
Set wsH = Worksheets("HOME")
For i = 1 To ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
Set oShp = wsH.Shapes(ws.Cells(i, "A").Value)
oShp.OnAction = "'" & ActiveWorkbook.Name & "'!" & ws.Cells(i, "D").Value
Next i
End Sub

BUT, if the list in ColA of ws ProjectNumbers change, I need the button caption names to change too.

Originally I recorded, then simply wrote: -

Sub AssignSheetName001()
Dim shtName As String
Dim shp As Shape
shtName = Sheet1.Range("A1").Value
ActiveSheet.Shapes.Range(Array("001")).Select
Selection.Characters.Text = shtName
End Sub

So, based on "Selection.Characters.Text", I tried to insert that to the following >>


Sub AssignShapeName()
Dim ws As Worksheet
Dim wsH As Worksheet
Dim i As Integer
Dim oShpText As Shape

Set ws = Worksheets("ProjectNumbers")
Set wsH = Worksheets("HOME")
For i = 1 To ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
Set oShpText = wsH.Shapes(ws.Cells(i, "A").Value)
oShpText.Selection.Characters.Name = ws.Cells(i, "A").Value
Next i
End Sub

However, it stops at line with " Object not supported "
oShpText.Selection.Characters.Name = ws.Cells(i, "A").Value

I have tried editing the code with the following variations >>

oShpText.Name = ws.Cells(i, "A").Value
oShpText.Text = ws.Cells(i, "A").Value
oShpText.Caption = ws.Cells(i, "A").Value
oShpText.ShapeRange.Name = ws.Cells(i, "A").Value
....and many other variations.

I'm still getting the same error message no matter what I do. Am I modifying the wrong part of the code or have I done something completely wrong?

Eventually I'd then like to combine both the assign macros and renaming to the same macro.

mana
03-19-2017, 06:01 AM
oShpText.TextFrame.Characters.Text
or
oShpText.DrawingObject.Text

paspuggie48
03-19-2017, 06:07 AM
Can't believe it Mana...spot on !!! MANY THANKS :hi: