Activate button when all fields populate

[surplusbc]

Hi everyone,

I have a button that I would like to have disabled until cells B2:B6 is populated. Can someone help with that code? Below is my original code as it stands right now.

The other thing is that cell B5 is a drop down box done by data validation, not a control box. The drop down has 3 selections. If you pick 2 of the selections, B6 should probably be populated by 5 digits. If you pick the other selection, "NEW", B6 will be populated by 5 digits followed by a letter, like 12345A. Is it possible to have a msgbox pop up if they select "NEW" in B5 and then type a number like 12345 with no letter, and have it say something like, "you need a letter". If that can be done, I'd appreciate that as well.

Utlimately, any help is appreciated as I have no idea what I'm doing right now.

Please Login or Register  to view this content.

[Charlie]

Here's the part for enabling/disabling the command button

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim Cell As Range

For Each Cell In Range("B2:B6")
If Cell.Value = "" Then
CommandButton1.Enabled = False
Exit Sub
End If
Next Cell

CommandButton1.Enabled = True

End Sub


"surplusbc" wrote:

>
> Hi everyone,
>
> I have a button that I would like to have disabled until cells B2:B6 is
> populated. Can someone help with that code? Below is my original code
> as it stands right now.
>
> The other thing is that cell B5 is a drop down box done by data
> validation, not a control box. The drop down has 3 selections. If you
> pick 2 of the selections, B6 should probably be populated by 5 digits.
> If you pick the other selection, "NEW", B6 will be populated by 5
> digits followed by a letter, like 12345A. Is it possible to have a
> msgbox pop up if they select "NEW" in B5 and then type a number like
> 12345 with no letter, and have it say something like, "you need a
> letter". If that can be done, I'd appreciate that as well.
>
> Utlimately, any help is appreciated as I have no idea what I'm doing
> right now.
>
>
>
> Code:
> --------------------
> Private Sub CommandButton1_Click()
> Dim NextRow As Long
> Application.ScreenUpdating = False
> Me.CommandButton1.Enabled = False
> With Worksheets("Admin")
> NextRow = .Range("A" & Rows.Count).End(xlUp).Row + 1
> Me.Range("B2:B6").Copy
> .Range("A" & NextRow).PasteSpecial Transpose:=True
> End With
> Me.Range("B2:B6").ClearContents
> Me.CommandButton1.Enabled = True
> Application.ScreenUpdating = True
> Run "Macro2"
>
> End Sub
> --------------------
>
>
> --
> surplusbc
> ------------------------------------------------------------------------
> surplusbc's Profile: http://www.excelforum.com/member.php...fo&userid=5377
> View this thread: http://www.excelforum.com/showthread...hreadid=507352
>
>