Require cell entries before macro button is visible

[KGee]

Is it possible to force users to make a valid entry in three different cells before a button with an assigned macro is visible or clickable?

For example, I want them to complete cells A5, B5 & C5. The first is a dept. code which can only be numeric (01 - 08). The second is the month (01 - 12). And the third would correspond to the year (2000 - maybe 2020?).

I have the workbook set to go to A5 on open and don't want them to leave A5 until a valid dept. code is entered. Then make it go to B5, etc. Once all three are complete and valid, I want the command button to be visible/clickable.

 

[ChronicFear]

The validation you can do with Data -> Validation. Just click the cell you want, then choose what type of data you want in the cell. You can also set custom warning messages if you'd like (eg. "There are no exclamation points in dates, moron."

As for limiting entry to only those three cells, I'd protect the whole worksheet and just unlock those three. Then when there is data in all three you can turn off the protection in the same code you use to make the macro button visible.

As for making sure your data is all entered before proceeding, I'd put some If statements in the Worksheet_Change event that looks to see if A5 is empty, then looks at B5, then at C5. If if gets down past C5, then unlock the sheet and make the button visible.

 

[mikerickson]

Put this in the sheets code module

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
ActiveSheet.Shapes("Button 2").Visible = (Application.CountA(Range("A5,B5,C5")) = 3)
End Sub

 

[mbdowty]

I'm sure there is a more sophisticated way, but you could use conditional formatting.

If you start with some blank cells and an "and" statement in your conditional formatting, you could get your cell with your button macro to go from black to white (for example). If your button is the origional color, it would look invisible to begin with and then seem to appear when the conditional format is met. I don't know if you could be more clever with conditional formatting and once the series of cells condition is met, the cell becomes unlocked.

I'm sure you could do it with some VBA/macro code as well.

 

[VoG]

Something like this (not completely tested).

In the Workbook code

Private Sub Workbook_Open()
Sheets("Sheet1").Select
Sheets("Sheet1").DrawingObjects("Button 1").Visible = False
Range("A5").Select
End Sub

In Sheet1's code module

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Intersect(Target, Range("A5,B5,C5")) Is Nothing Then
    If WorksheetFunction.CountA(Range("A5,B5,C5")) <> 3 Then
        MsgBox "You must complete A5:C5"
    Else
        DrawingObjects("Button 1").Visible = True
    End If
End If
End Sub

 

[KGee]

Thanks, I will give it a go when I return to work tomorrow. I guess the sheet protection is a not a bad idea as well and I saw code in the forum to enable/disable protection when using a password.

Kevin

 

[KGee]

I've got the validation setup the way I want, but I can't get the buttons with the macros to display.

I protected the sheet and hid the buttons from the Workbook - Open like so:

Private Sub Workbook_Open()
Range("D10").Select
ActiveCell.FormulaR1C1 = ""
Range("E10").Select
ActiveCell.FormulaR1C1 = ""
Range("C10").Select
ActiveCell.FormulaR1C1 = ""
Sheets("Sheet1").DrawingObjects("btn1").Visible = False
Sheets("Sheet1").DrawingObjects("btn2").Visible = False
Sheets("Sheet1").Protect "my_PW_here"
End Sub

Then in the Sheet1 module I added this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Intersect(Target, Range("C10,D10,E10")) Is Nothing Then
    If WorksheetFunction.CountA(Range("C10,D10,E10")) <> 3 Then
        MsgBox "You must provide valid entries for cells C10 - E10.", _
        vbOKOnly, "Input Required"
    Else
        Sheets("Sheet1").Unprotect "my_PW_here"
        Sheets("Sheet1").DrawingObjects("btn1").Visible = True
        Sheets("Sheet1").DrawingObjects("btn2").Visible = True
    End If
End If
End Sub

So when I have valid entries in all three cells, it doesn't unprotect the sheet. I have to do it manually from Tools-Protection-Unprotect Sheet. If I open the sheet and unprotect it from the menu before doing anything else, it still shows both buttons even though my three cells (C10-E10) are blank.

Is there something wrong with the code, or do I need to have it in a different section? When the sheet is protected, only cells C10-E10 can be selected. I'm not sure if the code above requires me to select a cell outside of that range to work properly, but ideally I would like it to actively hide both buttons whenever cells C10-E10 do not have valid entries from my data validation.