Use of variables within code for object text referencing and sheet naming

[mwpaull84]

Hi,

I've written some code that performs an action I need, but I have 64 actions that require code and wanted to get away with one macro rather than 64 each modified slightly according to the variables.

I have 64 different sheets (named 1-64 - hidden), 64 objects (rectangles) that contain numbers 1-64. A list tab with a list of numbers 1-64.

What I need to do is when a user clicks a rectangle it checks whether the number contained in that rectangle refers to a visible sheet (if it does it hyperlinks to it), if its hidden then the user is asked for an issue number, once confirmed the new number replaces the number in the box, the sheet is made visible and its name is changed to the new issue number and the new issue number is added alongside the original tab number name in a list tab. Oh and the boxes are currently 'greyed out' once they refer to a visible sheet the border/text becomes black.

The below works for once object, sheet, cell reference for issue number but as it stands i'm looking at writing code for 63 additional boxes rather than one variable code... can it be done?

Thanks for reading my first post/question - heres my code (apologies if it comes up a bit different to read)

Sub ChangeSheetTEST()
If Sheet3.Visible = True Then
Sheet3.Select
Range("d3").Select
Else
Dim myNum As Integer
myNum = Application.InputBox("Enter Issue Number")
ActiveSheet.Shapes("Rectangle 14").Select

Selection.Characters.Text = myNum
With Selection.Characters(Start:=1, Length:=3).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 16
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Sheet3.Visible = True
Sheet3.Name = myNum
Range("D3").Select
Sheets("Stats").Select
Range("e3") = myNum
Sheets("Experian (Home)").Select
ActiveSheet.Shapes("Rectangle 14").Select
Selection.ShapeRange.Fill.Visible = msoTrue
Selection.ShapeRange.Fill.Solid
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 65
Selection.ShapeRange.Fill.Transparency = 0#
Selection.ShapeRange.Line.Weight = 0.75
Selection.ShapeRange.Line.DashStyle = msoLineSolid
Selection.ShapeRange.Line.Style = msoLineSingle
Selection.ShapeRange.Line.Transparency = 0#
Selection.ShapeRange.Line.Visible = msoTrue
Selection.ShapeRange.Line.ForeColor.SchemeColor = 64
Selection.ShapeRange.Line.BackColor.RGB = RGB(255, 255, 255)

Range("D3").Select
End If
End Sub

 

[Norie]

Pass the issue number to the sub and use that when referring to the shape.

 

[mwpaull84]

Hi Norie thanks for your reply - most of my excel macro knowledge is self learnt through the record macro function in excel and then trying to make sense of the code. Are you able to example what you mean? thanks.

 

[Teeroy]

Application.Caller can provide a link back to the shape that calls a macro. E.g. the following will toggle the visible state of the sheet named in the text frame of the shape that called it (and you could have all 64 shapes referring to it).

Sub AutoShape1_Click()
x = Application.Caller
    Sheets(ActiveSheet.Shapes(x).TextFrame.Characters.Text).Visible = _
    Not (Sheets(ActiveSheet.Shapes(x).TextFrame.Characters.Text).Visible)
End Sub

Note that you'd want to have error checking to ensure that the sheet with that name existed.

 

[mwpaull84]

Thats great exactly what I needed - thanks!