Solved: Command button colour change if file missing

[Gil]

Hello
I started off with this code in a Command Button


Private Sub WillesOpen_Click()
Dim fPath As String, fName As String
'You must change this line to your desired folder location!
fPath = "G:\Record\All Changes\Willes\"
' fPath = "f:\Temp\"
'Change Book2.xls to the file to open
fName = "Search tool2Willes.xls"
On Error Resume Next
'Workbooks(fName).Activate use this code to activate an open workbook
Workbooks.Open fPath & fName
'If Excel cannot activate the book, then it's not open, which will
' in turn create an error not of the value 0 (no error)
If Err = 0 Then
Workbooks("Change open tool.xls").Activate
'Exit macro if no error
End If
Err.Clear 'Clear erroneous errors
Workbooks("Change open tool.xls").Close
End Sub


but found that if the eventual target file was missing it was irritating there was no warning. So I added this code to it


Private Sub WillesOpen_Click()
Dim fPath As String, fName As String

Dim fso
Dim file As String
file = "G:\Record\All Changes\Willes\Willes Data.xls" ' change to match the file w/Path
Set fso = CreateObject("Scripting.FileSystemObject")
If Not fso.FileExists(file) Then
MsgBox file & " has not been loaded.See Gil for update"
Else

'You must change this line to your desired folder location!
fPath = "G:\Record\All Changes\Willes\"
' fPath = "f:\Temp\"
'Change Book2.xls to the file to open
fName = "Search tool2Willes.xls"
On Error Resume Next
'Workbooks(fName).Activate use this code to activate an open workbook
Workbooks.Open fPath & fName
'If Excel cannot activate the book, then it's not open, which will
' in turn create an error not of the value 0 (no error)
If Err = 0 Then
Workbooks("Change open tool.xls").Activate
'Exit macro if no error
End If
Err.Clear 'Clear erroneous errors
Workbooks("Change open tool.xls").Close
End If
End Sub


which works ok as the message box pops up. However is there a way I can have a visual so I can just ignore the command button thus not selecting and then cancelling from the message box. For example to change the background colour of the button if the file is not present. Perhaps red if the file is not there but green when it is.
Any ideas and suggestions would be appreciated. I think I would like to keep the message box as well just in case of any colour blindness.

Gil

[GTO]

Is the button on a userform or on a worksheet?

Either way, you might want to disable the button right below the msgbox. If on a userform, I was just thinking that you might want to create the FSO object in the form's initialize event, check for the file there, and have the button already disabled. You could either change the button's caption ("No File") or add a label to the form.

[Gil]

Hello GTO
The button is one of several on a userform
Gil

[Bob Phillips]

Why not just disable it



Me.cmdName.Enabled = False

[Gil]

Hello xld
When the target file is added or removed for any reason I wanted the colour indication to occur automatically as does the message box.
Gil

[Bob Phillips]

Disabling could be done the same, it gturns lighter gray as well.

[Gil]

Hello xld
That would be ok. Do I use the code you suggested and where does it fit in please.
Gil

[Bob Phillips]

I can't say I know as I can't see that code in context. Is it the code for the button you want to colour/disable (that seems pointless, the code has already executed by then)?

[Gil]

Hello xld
It is the button I want to colour or disable when the userform is opened if the target file is missing. If the colour change is ignored and the button pressed the message box then pops up. Of course if the button were disabled then no action would happen. I have attached an example file if that helps.
Gil

[Bob Phillips]

Try this



Private Sub UserForm_Activate()
Dim fso
Dim file As String

file = "G:\Record\All Changes\Willes\Willes Data.xls" ' change to match the file w/Path
Set fso = CreateObject("Scripting.FileSystemObject")
If Not fso.FileExists(file) Then

MsgBox file & " has not been loaded.See Gil for update"
Me.WillesOpen.Enabled = False
Else

Me.WillesOpen.Enabled = True
End If

End Sub

Private Sub WillesOpen_Click()
Dim fPath As String, fName As String

'You must change this line to your desired folder location!
fPath = "G:\Record\All Changes\Willes\"
' fPath = "f:\Temp\"
'Change Book2.xls to the file to open
fName = "Search tool2Willes.xls"
On Error Resume Next
'Workbooks(fName).Activate use this code to activate an open workbook
Workbooks.Open fPath & fName
'If Excel cannot activate the book, then it's not open, which will
' in turn create an error not of the value 0 (no error)
If Err = 0 Then
Workbooks("Change open tool.xls").Activate
'Exit macro if no error
End If
Err.Clear 'Clear erroneous errors
Workbooks("Change open tool.xls").Close
End Sub

[Gil]

Hello xld
Thank you for your suggestion and reply. I have changed your code slightly as my userform has a number of command buttons on it which may have files missing from the target location, therefore the pop up message has been deleted as the command buttons affected do disable and the text goes grey. However when a file is replaced into the target location the command button does not enable.
Any ideas Gil

Private Sub UserForm_Activate()

Dim file As String

file = "G:\Record\All Changes\Willes\Willes Data.xls" ' change to match the file w/Path
Set fso = CreateObject("Scripting.FileSystemObject")
If Not fso.FileExists(file) Then
Me.WillesOpen.Enabled = False
Else

Me.WillesOpen.Enabled = True
End If

End Sub

p.s. I took out enough to stop the pop up but I can see that it still needs tidying up. I just don't know what.

[Bob Phillips]

Does the file get replaced when the userform is active, or do you have to ire the form up again?

[Gil]

Hello xld
Normally the file would be reinstated and then the form would be fired up.
Gil

[GTO]

I am a bleary eyed donkey admittedly, but I am not getting why we are checking to see if 'Willes Data.xls' exists, but then opening 'Search tool2Willes.xls' without checking to see if it exists.

Is 'Search tool2Willes.xls' linked to 'Willes Data.xls'? Why not check to see if 'Search tool2Willes.xls' exists before trying to open it?

[Gil]

Hello GTO
The project I have is like a tree. It starts with a userform that has several command buttons on it. In turn each opens another userform that has more command buttons each with a specific function. One of these is to open a file like 'Willes Data.xls'. If for example 'Willes Data.xls' does not exist or has been removed I wanted a visual indication i.e. the button changing colour or as I already have and works, a pop up message. The colour indication or the disable/enable with the label on the button changing to grey that xld suggested would be ok, it would just save me having to press a button,getting the message, and then closing the box. The code does the disable if the file is missing but not the enable if the file is reinstated.
Now that explanation will make you bleary eyed.
Gil

[Gil]

Hello xld
I know I doctored your code above to stop the pop up message and now when run it does disable the command buutons that have the target files missing but I think the Set fso part needs taking out or does it.

I have dabbled all day to enable the button or buttons with no success.
Maybe I am missing the point but I cannot see where.
Gil

[GTO]

Hi Gil,

Let's take a step back. Presuming that 'Willes Data.xls' does exist in the correct folder before the userform is run. Does the button get enabled properly?

Mark

[Gil]

Hello GTO
Everything works properly. It is only when the code suggested is introduced that the button becomes disabled if the target file is missing.When the target file is reinstated the button fails to restore.
Gil

[GTO]

Hi Gil,

Please look at the code, as I changed where to look for the file to ThisWorkbook.Path, just for convenience of testing.

Although you'll see that I changed declaring fso to a Static, this would not make a difference as to the code's working. You will see that Bob's code works fine upon clicking the buttons on the sheet (the code to the buttons resides in the sheet's module).

Show the form, then .Hide it with either of the two buttons on the form. If you then switch over to explorer and install the needed workbook, and go back and click the Shwo Form button again, you will see that the button's enabled status does change.

Mark

[Gil]

Hello GTO
Just using this part of the code I am now able to fire up my Userform and have the Command button enabled or disabled depending on whether the target file for that button exists.

Private Sub UserForm_Activate()
Static fso As Object
Dim file As String

'file = "G:\Record\All Changes\Willes\Willes Data.xls" ' change to match the file w/Path
file = "G:\Record\All Changes\Willes\Willes Data.xls"
If fso Is Nothing Then Set fso = CreateObject("Scripting.FileSystemObject")
If Not fso.FileExists(file) Then

Me.WillesOpen.Enabled = False
Else
Me.WillesOpen.Enabled = True
End If

End Sub

Now to expand on that if I have several other buttons that have different target files there or not do I add to the code something like this.

Private Sub UserForm_Activate()
Static fso As Object
Dim file As String

'file = "G:\Record\All Changes\Willes\Willes Data.xls" ' change to match the file w/Path
file = "G:\Record\All Changes\Willes\Willes Data.xls,G:\Record\All Changes\Willes5\Willes5 Data.xls,G:\Record\All Changes\Willes6\Willes6 Data.xls,G:\Record\All Changes\Willes7\Willes7 Data.xls
If fso Is Nothing Then Set fso = CreateObject("Scripting.FileSystemObject")

If Not fso.FileExists(file) Then
Me.WillesOpen.Enabled = False
Me.Willes5Open.Enabled = False
Me.Willes6Open.Enabled = False
Me.Willes7Open.Enabled = False
Else
Me.WillesOpen.Enabled = True
Me.Willes5Open.Enabled = True
Me.Willes6Open.Enabled = True
Me.Willes7Open.Enabled = True
End If

End Sub
Obviously the above does not work but I hope it explains the goal I am after.
Gil

[Bob Phillips]

No, you set file for the first file, test that and set its corresponding button. Then you set the second file, test it, and set its corresponding button, and so on.

[Gil]

Hello xld
I know it's like pulling teeth with me but I am trying.Very trying.
Well,if this is what you mean it works for me.
Gil

Private Sub UserForm_Activate()
Static fso As Object
Dim file As String

'file = "G:\Record\All Changes\Willes\Willes Data.xls" ' change to match the file w/Path

file = "G:\Record\All Changes\Willes\Willes Data.xls"
If fso Is Nothing Then Set fso = CreateObject("Scripting.FileSystemObject")
If Not fso.FileExists(file) Then
Me.WillesOpen.Enabled = False
Else
Me.WillesOpen.Enabled = True
End If

file = "G:\Record\All Changes\Willes5\Willes5 Data.xls"
If fso Is Nothing Then Set fso = CreateObject("Scripting.FileSystemObject")
If Not fso.FileExists(file) Then
Me.Willes5Open.Enabled = False
Else
Me.Willes5Open.Enabled = True
End If

file = "G:\Record\All Changes\Willes6\Willes6 Data.xls"
If fso Is Nothing Then Set fso = CreateObject("Scripting.FileSystemObject")
If Not fso.FileExists(file) Then
Me.Willes6Open.Enabled = False
Else
Me.Willes6Open.Enabled = True
End If

file = "G:\Record\All Changes\Willes7\Willes7 Data.xls "
If fso Is Nothing Then Set fso = CreateObject("Scripting.FileSystemObject")
If Not fso.FileExists(file) Then
Me.Willes7Open.Enabled = False
Else
Me.Willes7Open.Enabled = True
End If

End Sub

[Gil]

Hello all
Nearly forgot the most important thing.

Many thanks to xld & GTO for their help support and above all patience in helping me resolve my quest.
Cheers Gil:beerchug:

[GTO]

Nicely done. One thing though, just to understand checking to see if fso is an existing object. We only need to do this once, near the top of the sub. See if this makes sense.


Private Sub UserForm_Activate()
Static fso As Object
Dim file As String

'// We want to grab FileSystemObject and set a reference to it just once, so //
'// that we can use the 'stuff' in it, like .FileExists. So, the first time //
'// the Activate occurs, fso IS nothing, and we create the Object. //
'// After that, as we declared the object as Static, as long as the userform //
'// stays in memory, we'll fail the test (that is, fso will NOT be Nothing), and//
'// we don't spend time creating it. //
'// So you see, we only need to check once, ea time we activate the form, just //
'// so we don't try and use .FileExists before making sure it can be used. //
If fso Is Nothing Then Set fso = CreateObject("Scripting.FileSystemObject")

file = "G:\Record\All Changes\Willes\Willes Data.xls"
If Not fso.FileExists(file) Then
Me.WillesOpen.Enabled = False
Else
Me.WillesOpen.Enabled = True
End If

file = "G:\Record\All Changes\Willes5\Willes5 Data.xls"
If Not fso.FileExists(file) Then
Me.Willes5Open.Enabled = False
Else
Me.Willes5Open.Enabled = True
End If

'...and so on...
End Sub


Mark

[Gil]

Hello GTO
Tried it done it and looks good.
All done & dusted
Many thanks
Gil