Enable button if specific cells have values

[bobbieatendido]

Hi,

May i please ask for help on this excel problem i have?

I have 3 buttons, which already have macro's.

I need these buttons to only be clickable when all cells have values in them.

Note: 9 are dropdown lists and 5 are manually entered.

Dropdown: C4, C6, (C14 D14 E14 F14 G14), M8 and M12
Manually entered: C8, C10, (C12 E12) and M1

 

[dmt32]

Hi welcome to Forum.

You don't give much detail but I am guessing your buttons are ActiveX CommandButtons on your worksheet?

You could try something like this & see if does what you want:


Place code in your worksheets code page:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim RequiredCells As Range
    Dim Enabled As Boolean
    
    Set RequiredCells = Me.Range("C4,C6,C8,C10,C12,C14:G14,E12,M1,M8,M12")
    
    If Not Intersect(Target, RequiredCells) Is Nothing Then
    
    Enabled = CBool(Application.CountA(RequiredCells) = RequiredCells.Count)


    
    Me.CommandButton1.Enabled = Enabled
    Me.CommandButton2.Enabled = Enabled
    Me.CommandButton3.Enabled = Enabled
    
    Else
    Set RequiredCells = Nothing
    End If
    
End Sub

Dave

 

[bobbieatendido]

Hi Dave,

Thank you for your reply and welcome.


My sheet looks like this: https://img42.com/Eu8BT


The buttons are, Button (Form Control)
I'm not really good at excel macros so what i did was, i duplicated the sheet to a new excel, recorded a macro then copied that macro code to the Button (Form Control). Is it different to an ActiveX Commandbutton?


I also renamed my button to record, print and new. do i change:


Me.CommandButton1.Enabled = Enabled
Me.CommandButton2.Enabled = Enabled
Me.CommandButton3.Enabled = Enabled


to


Me.record.Enabled = Enabled
Me.print.Enabled = Enabled
Me.new.Enabled = Enabled


Thank you so much for the help.

 

[bobbieatendido]

Also, do i put the code to,
Developer > Visual Basic > (Sheetname) > then paste the code?

Sorry for too many questions.

Thank you again.

 

[bobbieatendido]

Nevermind, i got it:

i added this to the module:
<code style="margin: 0px; padding: 0px; font-style: inherit; font-weight: inherit;">Public Function BlankCells() As Boolean
Dim c As Range
BlankCells = False
For Each c In ActiveSheet.Range("C4,C6,C14,D14,E14,F14,G14,M8,M12,C8,C10,C12,E12,M1")
If IsEmpty(c) Then
BlankCells = True
Exit Function
End If
Next c
End Function</code>
and this to very top of each individual macro, except the delete all. :

<code style="margin: 0px; padding: 0px; font-style: inherit; font-weight: inherit;"> If BlankCells Then Exit Sub</code>

 

[dmt32]

Glad figured something for yourself but can do what you want without looping:

Function BlankCells() As Boolean
    Dim RequiredCells As Range
    Set RequiredCells = Range("C4,C6,C8,C10,C12,C14:G14,E12,M1,M8,M12")
    BlankCells = CBool(Application.CountA(RequiredCells) = RequiredCells.Count)
End Function

Dave