Testing if Control is still active?

[MikeSmuts]

I would appreciate it if somebody could help me find a way to determine if a certain control in Excel is still active.

The following line of code in my application invokes the straight connector:

CommandBars.FindControl(ID:=2640).Execute

The user then adds a straight connector to link 2 shapes (there are many shapes in the worksheet). What code will help me determine if the connector has been drawn (ie the control has run its course so to speak), or that the Esc key was used to terminate the control?

All / any help appreciated!

 

[SkipVought]

Hi,

Still working on that gantt chart, eh?

How are you referencing the control(s) that are under program control? Maybe some code would help.

Skip,

_[red][/red]

 

[PHV]

Just to know: why not using adhoc tools like msproject (or even Visio) ?

 

[MikeSmuts]

Hi Skip!

Yep, still at it. Following on from the other night, it occurred to me that it was rather silly to try and duplicate a command that was already available in Excel (ie the straight connector).

No sooner did I think I had it sorted, when I ran into other problems. Check the code:
____________________________________________________________

Private Sub Task4_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)

If Button = 1 And CurType = 3 Then
CommandBars.FindControl(ID:=2640).Execute 'Invoke Straight Connector
Call Connect
End If

End Sub
____________________________________________________________

Private Sub Connect()

Set ws = Worksheets("Sheet1")

'Find temporary connector and set variables to indicate which tasks are being connected
For Each sh In Shapes
If sh.Connector = True Then
If sh.ConnectorFormat.Type = msoConnectorStraight Then
sh.Name = "TempConnector"
Set FirstBar = sh.ConnectorFormat.BeginConnectedShape
Set SecondBar = sh.ConnectorFormat.EndConnectedShape
End If
End If
Next sh

'Delete the temporary connector if it exists
ws.Shapes("TempConnector").Delete

'Set connection name
ConnName = FirstBar.Name & "conn" & SecondBar.Name

'Add new connector and set properties
With ws.Shapes.AddConnector(msoConnectorElbow, 10, 10, 10, 10)
.Name = ConnName
.Line.EndArrowheadStyle = msoArrowheadTriangle
.Line.EndArrowheadLength = msoArrowheadShort
.Line.EndArrowheadWidth = msoArrowheadNarrow
.OnAction = "EditConnectorMenu"
End With

'Set connector formats
With ws.Shapes(ConnName).ConnectorFormat
.BeginConnect FirstBar, ConnectionSite:=4
.EndConnect SecondBar, ConnectionSite:=2
End With

End Sub

The problem is that after the connector command is invoked, the code goes straight to the Call Connect routine and of course the straight connector that it tests for does not exist yet, causing an error.

I’m not sure how to “suspend” further code execution to allow the connector call to be completed first. Any ideas?

 

[MikeSmuts]

Hi PHV

I suffer from low blood pressure and for me VBA is like trying to write a "best seller" novel in a foreign language - it gets the blood pressure up

Actually I've never been one to learn coding in a new language with the "Hello World!" type manuals.

The Gantt Chart is the basis for a larger application that I wrote some time ago for analysing property development project viabilities. I'm busy updating it and this is my first real encounter with a more graphically based UI. The first version simply used shaded cells to indicate task bars (from which dates were then extracted for use in the rest of the application). The new version will allow critical path analysis etc. (plus look a whole lot more professional!)

 

[Bong]

Why not, as PHV suggested, us MSProject? It also supports VBA and the graphics are already available.

_________________
Bob Rashkin