Adding/Deleting Shapes

Hi again,

Attached is a simplified workbook showing the approach I mentioned above. The useful aspect of it is that it uses only a single routine for opening/closing a switchgear item, regardless of how many such items the worksheet contains. The code is as follows:

Option Explicit


Sub OperateSwitch()

    Const sBUTTON_PREFIX    As String = "btn"
    Const sSHAPE_PREFIX     As String = "shp"

    Const lGREEN            As Long = 32768
    Const lRED              As Long = 255

    Const iROTATION         As Integer = -30
    Const sCLOSE            As String = "Close"
    Const sOPEN             As String = "Open"
    Const iLEFT             As Integer = 5
    Const iTOP              As Integer = -2

    Dim sSwitchName         As String
    Dim rSwitchCell         As Range
    Dim rDataTable          As Range
    Dim shpButton           As Shape
    Dim shpSwitch           As Shape
    Dim rState              As Range
    Dim sState              As String
    Dim iRowNo              As Integer

    Set rDataTable = ActiveSheet.Range("ptrDataTable")

'   Identify the button which called this routine - this in turn will identify
'   which switch to operate
    Set shpButton = ActiveSheet.Shapes(Application.Caller)

'   Determine the name of the associated switch shape
    sSwitchName = Replace(Application.Caller, sBUTTON_PREFIX, vbNullString)

'   Locate the above switch in the data table
    With rDataTable.Columns(1)

        Set rSwitchCell = Nothing
        Set rSwitchCell = .Cells.Find(What:=sSwitchName, _
                                      LookIn:=xlValues, LookAt:=xlWhole)

    End With

    If Not rSwitchCell Is Nothing Then

'         Create a reference to the required switch shape
          Set shpSwitch = ActiveSheet.Shapes(sSHAPE_PREFIX & sSwitchName)

'         Determine the next state of the switch - i.e. Open if the swicth is
'         currently closed, and Closed if the switch is currently open
          Set rState = rSwitchCell.Offset(0, 1)
              sState = rState.Value

'         Change the state of the required switch and the colour of the associated button
          With shpSwitch

              If sState = sOPEN Then

                    .IncrementLeft -iLEFT
                    .IncrementTop -iTOP
                    .Rotation = iROTATION
                    rState.Value = sCLOSE

                    shpButton.TextFrame.Characters.Font.Color = lGREEN

              Else: .IncrementLeft iLEFT
                    .IncrementTop iTOP
                    .Rotation = 0
                    rState.Value = sOPEN

                    shpButton.TextFrame.Characters.Font.Color = lRED

              End If

          End With

    Else: MsgBox "The table does not contain data for switch " & sSwitchName

    End If

End Sub

Hope this helps.

Regards,

Greg M