Greyed-out Buttons

[joewood86]

Hi

I'm fairly new to Excel Macroing and I've develeoped a spreadsheet which produces a report based on the information contained within a row. The user selects the row they want to make a report for, then click a button which runs a macro to copy the contents to each cell into a sheet called "Report", which has the layout of the report I print. The macro also opens Word and copies the cells which contain the report information into a blank document, then saves it with a sequential file name, before closing Word.

Is it possible to have the "Make Report" button greyed-out unless a specific field has been filled in? Essentially when the user selects the row they want to make the report for I want the macro to check if a specific cell within that row is blank or not, and if it is blank the "Make Report" button should be grey.

Any ideas?

Joe

[royUK]

It depends where you got the button from - the Forms toolBar or Controls toolBox. they work differently.

Also, I would think that the report could be filled simply by using VLOOKUP

[dominicb]

Good afternoon joewood86

If you are using buttons from the Control Toolbox toolbar, then something like this would toggle the state of the .Enabled property for CommandButton1, depending on whether cell A1 was filled or not :

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("A1") = "" Then
ActiveSheet.OLEObjects("CommandButton1").Object.Enabled = False
Else
ActiveSheet.OLEObjects("CommandButton1").Object.Enabled = True
End If
End Sub

This needs to go in the relevant sheet's event procedure module.

HTH

DominicB

[joewood86]

Hi

I've got it sorted now, through the code that DominicB and from royUK's advice I've used a Message Box to say that the field must be completed to create a report. The whole thing works with one big if statement and if the box isn't complete the rest of the macro is ignored.

Many thanks for your help and advice

Joe