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

It doesn't look like my reply went through earlier, so please excuse if there is a duplicate...

Thank you Greg! The solution you suggested is along the lines of what I'm trying to accomplish with a couple of caveats... all of the switches in your worksheet are 2-way, and I'm having trouble with the 3-ways. Also, I've tried working inside of the workbook you shared, but I can't seem to figure out how to copy or make changes to the existing switches without causing errors in the code... For example, my setup doesn't have a switch named "F72Q2G". If I attempt to change the name of this to "F72Q1G", I get errors. If I copy a line, switch, and button, and attempt to rename them using the table, the button name changes, but the control still operates the copied switch, not the new. I've tried changing the switch name but I'm just not getting anywhere.

Yes, these are disconnector/grounding switches. I wasn't expecting to get a response from anyone who has experience in the electrical world -- very nice surprise! I'm a Sr. Field Service Eng. with about 21yrs. under my belt, so not quite as extensive as your background. I do enjoy wearing many hats from designing, testing, commissioning, and training, mostly in the protection and automation relaying; not so much with apparatus. What's the industry like in Ireland? Are you investing a lot in renewables these days? I don't imagine solar is very reliable, but perhaps wind? Maybe off-shore wind?

Thank you again for your insight and support!

Best Regards,

Shannon