Checking for Open Workbook

[DonovanKB]

Hi everyone,

I'm new here and quite new with VBA (excel) so be gentle Sorry if this is a long opening but I am trying to be as clear as possible!

I am currently trying to sort out a pretty basic button control scheme for a large project. This will most likely be a temporary set up but it will allow me to check how things operate in the big picture.

I will try to describe my goal and how I would like it to achieve it.

I currently have two sheets, one is Control.xls and the other is ggt.xls.

In control.xls I have two images, one is black, the other is red and they are ON TOP of each other. When the black image is visible it indicates that ggt.xls is not open and the 'button' is enabled, when the red image is visible it indicates that ggt.xls is open and the 'button' is disabled. The button (the black image) basically opens ggt.xls. I want the choice of opening ggt.xls up to the user and not a forced open.

Upon opening Control.xls I have an Auto_Open macro which calls another macro "buttonCheck".

The button check macros purpose is to check whether ggt.xls is open or not. After this check is done the appropriate black or red image is brought to the front on Control.xls so as to enable/disable the button. During this check I am trying to avoid ggt.xls being 'activated' and brought to the front. Since I want this check to be looped at fairly short time intervals I don't want the ggt.xls workbook popping up in front of control.xls every 5-10 seconds!


Due to my lack of skill this is all I have come up with so far haha.

The comma'd out parts are what I have tried to record for sending the images forwards and back.

Sub buttonCheck()

On Error GoTo IsClosed
If Not Workbooks("ggt.xls") Is Nothing Then
MsgBox "Workbook is open"
Exit Sub
End If

IsClosed: MsgBox "Workbook is not open."

'ActiveSheet.Shapes("Picture 14").Select
'Selection.ShapeRange.ZOrder msoSendToBack

End Sub

So, in short, i need a macro that checks whether a workbook is open or not without activating it. Depending on the workbooks status i need a certain image to move forwards or backwards.

Apologies this is such an extensive read.

Any replies would be greatly appreciated!!!!!

-Best regards,
Donovan KB.

[shg]

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[DonovanKB]

Thanks shg for the quick reply

Sorry this will make me look like a dunce but I am having another issue

When the buttonCheck macro runs, i get an error saying "End Sub" was expected directly below Sub buttoncheck()... ???

Does the Function - End Function require its own module or something similar?

Sub buttonCheck()

Function WkbIsOpen(sName As String) As Boolean
On Error Resume Next
WkbIsOpen = Len(Workbooks(sName).Name) > 0
End Function

If WkbIsOpen("ggt.xls") Then
MsgBox ("Check")

End Sub

[DonovanKB]

Nevermind I figured it out and then modified it slightly.

In future reference if anyone wants the same thing this is what I used.

Sub buttonCheck()

If WkbIsOpen("ggt.xls") = True Then
MsgBox ("Work Book is Open")
End If

End Sub

Function WkbIsOpen(sName As String) As Boolean
On Error Resume Next
If Len(Workbooks(sName).Name) > 0 Then
WkbIsOpen = True
Else: WkbIsOpen = False
End If

End Function

Thanks