Hide Command Button Until All Sections Are Filled In

[SyCoDeath]

Hi,

Bit of a newbie to VB.

I have an email facility on a document i am making. However; I would like this button to be either hidden or out of use until all Sections/Cells are filled in fully. I have set it up so that you cannot save or print the document but this one is making me go crazy.

Does anyone know how i can do this? Any help will be appreciated.

Thanks in advance

 

[evnoort]

try this

Private Sub Worksheet_Change(ByVal Target As Range)
If Cells(2, 2).Value <> "" And Cells(3, 2) <> "" And Cells(4, 2) <> "" Then
   CommandButton1.Visible = True
Else
   CommandButton1.Visible = False
End If

End Sub

 

[SyCoDeath]

Thanks for the speedy reply...

I am lost though, I have 2 Sheets within my Workbook (RC & EB)

The ranges of both worksheets that need to be filled in before the command button works are the following.

myRanges = Array(Me.Worksheets("RC").Range("B7,F7,I7,B12,B17,B22,B28,B40,B45,G45,B50,G50,B55,B60,G60,M59,L4,L9,L14,L23"))

myRanges = Array(Me.Worksheets("EB").Range("D4,I4,L4,M4,M4,O4,P4,Y4,Z4,AA4,AB4,AC4,AD4,AE4,AF4,AG4,AJ4,AK4"))

I am not sure how to utilize your code. I am still trying to learn...

Thanks again if you can help.

Craig

 

[evnoort]

You can use sheets("rc").range("B7").value or sheets("rc").cells(7,2).value in the code they are the same.

 

[Domenic]

Maybe...

[font=Verdana][color=darkblue]Private[/color] [color=darkblue]Sub[/color] Worksheet_Change([color=darkblue]ByVal[/color] Target [color=darkblue]As[/color] Range)

    [color=darkblue]Dim[/color] Rng1 [color=darkblue]As[/color] Range
    [color=darkblue]Dim[/color] Rng2 [color=darkblue]As[/color] Range
    [color=darkblue]Dim[/color] Rng3 [color=darkblue]As[/color] Range
    
    [color=darkblue]Set[/color] Rng1 = Me.Range("B7,F7,I7,B12,B17,B22,B28,B40,B45,G45,B50,G50,B55,B60,G60,M59,L4,L9,L14,L23")

    [color=darkblue]Set[/color] Rng2 = Me.Range("D4,I4,L4,M4,M4,O4,P4,Y4,Z4,AA4,AB4,AC4,AD4,AE4,AF4,AG4,AJ4,AK4")
    
    [color=darkblue]Set[/color] Rng3 = Application.Union(Rng1, Rng2)
    
    [color=darkblue]If[/color] Application.Intersect(Target, Rng3) [color=darkblue]Is[/color] [color=darkblue]Nothing[/color] [color=darkblue]Then[/color] [color=darkblue]Exit[/color] [color=darkblue]Sub[/color]

    [color=darkblue]If[/color] Application.WorksheetFunction.CountA(Rng3) = Rng3.Count [color=darkblue]Then[/color]
       CommandButton1.Visible = [color=darkblue]True[/color]
    [color=darkblue]Else[/color]
       CommandButton1.Visible = [color=darkblue]False[/color]
    [color=darkblue]End[/color] [color=darkblue]If[/color]

End [color=darkblue]Sub[/color]
[/font]

 

[SyCoDeath]

evenoort:
I am using the following mate.

Private Sub Worksheet_Change(ByVal Target As Range)
If Sheets("RACECARD ACTIVITY REQUEST").Range("B7, F7, I7, B12, B17, B22, B28, B40, B45, G45, B50, G50, B55, B60, G60, M59, L4, L9, L14, L23").Value <> "" Then
   EMAIL.Enabled = True
Else
   EMAIL.Enabled = False
End If
End Sub

But it only works if B7 is removed, it doesnt work if you delete any of the others. Do you know why that would be please.

Domenic:

When i used your code, it would always be either invisible or inactive. No matter what i do. Do you know why that would be please.

Thanks for all your help guys, it is really appreciated.

 

[Domenic]

With regards to the code I offered, when a cell in the worksheet is changed, it first checks whether the cell falls within the two specified ranges. If not, it exits the sub. Otherwise, it counts the number of cells within the two specified ranges that are not empty. If it equals the number of cells in the ranges, the Visible property for the command button is set to True. Otherwise, it's set to False. Isn't this what you're looking for?

 

[evnoort]

Hi SyCoDeath, did you solve your problem yet? (was on short holiday)

 

[SyCoDeath]

Guys,

Thanks for your replies.

Domenic - That is what i need, however, when you remove the data from a selected range, the box will disappear or be greyed out as it should do, but even when you put the data back in the range it remains greyed out / invisible.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Rng1 As Range
    Dim Rng2 As Range
    Dim Rng3 As Range
    
    Set Rng1 = Me.Range("B7,F7,I7,B12,B17,B22,B28,B40,B45,G45,B50,G50,B55,B60,G60,M59,L4,L9,L14,L23")
    Set Rng2 = Me.Range("D4,I4,L4,M4,M4,O4,P4,Y4,Z4,AA4,AB4,AC4,AD4,AE4,AF4,AG4,AJ4,AK4")
    
    Set Rng3 = Application.Union(Rng1, Rng2)
    
    If Application.Intersect(Target, Rng3) Is Nothing Then Exit Sub
    If Application.WorksheetFunction.CountA(Rng3) = Rng3.Count Then
        EMAIL.Enabled = True
    Else
        EMAIL.Enabled = False
    End If
End Sub

Maybe i have done something wrong but im getting more and more confused.

evnoort - Hope you had a good break.

Yours works but only for the first range B7. I can delete all the other boxes and it makes no difference. IT just only seems to work with B7

Do you know why that would be, as like i say i clearly don't know enough...

Thanks guys

 

[0lzi]

im no VBA expert but i do programming in different languages, and it sounds to me like you need to loop that operation/function untill the button has been pressed.

im sure one of the VBA guys could whip up some code for a while loop ( or VBA equivalent) to keep checking the cell value until Email.button = pressed

hope this can be some help