Run Macro Only If 4 Cells Are Filled In By User In VBA
 

Hi again.

I have a sheet where the user is required to answer 4 question befor
continuing.
The user has to enter data into 4 cells before being allowed to pres
the continue button.

I have allready created the button to run the next operation whe
pressing "Continue", but I would like the button to be dis-enable
until all 4 cells are completed.

The cell refferences a

E6, E8, E10, and E12

The button is still currently named "CommandButton1_Click()" but thi
will be changed later.

I can use the code:

ActiveSheet.Shapes("CommandButton1").Visible = False

to hide the button if all cells are not completed, but I would prefe
to still see it but have it dis-enabled as if in the contro
properties. If I try to record a macro to disenable the button, th
only code that is recorded is the selection of the button.
Is there a way that this can be written in code? And where would
place the code, would it be in the Worksheet Selection_Change area?

Thanks For Any Help You Can Give.


Celtic_Avenger
:confused: :confused: :confused: :confused: :confused

--
Celtic_Avenge
-----------------------------------------------------------------------
Celtic_Avenger's Profile: http://www.excelforum.com/member.php...fo&userid=1410
View this thread: http://www.excelforum.com/showthread.php?threadid=26137

Run Macro Only If 4 Cells Are Filled In By User In VBA
 

Hi Celtic_Avenger,

Try something like:

Private Sub CommandButton1_Click()
Dim rng As Range
Set rng = Range("E6, E8,E10,E12")
If Application.CountA(rng) < 4 Then
MsgBox "All four questions need to be answered", _
vbInformation
Else
'\\ optionally hide the button
'Me.CommandButton1.Visible = False
'\\ or disable the button
'Me.CommandButton1.Enabled = False

'Your continue code
End If
End Sub


---
Regards,
Norman



"Celtic_Avenger" wrote in
message ...

Hi again.

I have a sheet where the user is required to answer 4 question before
continuing.
The user has to enter data into 4 cells before being allowed to press
the continue button.

I have allready created the button to run the next operation when
pressing "Continue", but I would like the button to be dis-enabled
until all 4 cells are completed.

The cell refferences a

E6, E8, E10, and E12

The button is still currently named "CommandButton1_Click()" but this
will be changed later.

I can use the code:

ActiveSheet.Shapes("CommandButton1").Visible = False

to hide the button if all cells are not completed, but I would prefer
to still see it but have it dis-enabled as if in the control
properties. If I try to record a macro to disenable the button, the
only code that is recorded is the selection of the button.
Is there a way that this can be written in code? And where would I
place the code, would it be in the Worksheet Selection_Change area?

Thanks For Any Help You Can Give.


Celtic_Avenger
:confused: :confused: :confused: :confused: :confused:


--
Celtic_Avenger
------------------------------------------------------------------------
Celtic_Avenger's Profile:
http://www.excelforum.com/member.php...o&userid=14101
View this thread: http://www.excelforum.com/showthread...hreadid=261374