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 using


if Sheets("RACECARD ACTIVITY REQUEST").Range("B7") and Sheets("RACECARD ACTIVITY REQUEST").Range("C7") and .... then</pre>

 

[Domenic]

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

If you'd like, I can email you a sample file. If so, send me your email address via Private Message.

 

[Domenic]

Sample file sent...

 

[SyCoDeath]

Demonic.

I am going to delete this file soon... It works perfectly on yours, but as soon as i use it in mine the Command Button just vanishes and never returns. If i use the .Enabled option it just remains greyed out rendering the button useless.

I have now even deleted all of my other macros and still not working...

I have even changed the code to 3 different options, still not working...

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:D8,B12:J13,B17:B18,B22:J24,B28:J36,B40:J41,B45:E46,B50:E51,B55:J56,B60:E61,F7:G8,G45:J46,G50:J51,G60:J61,I7:J8,L4:O5,L9:U10,L14:U19,L23:U28,M59:U61,Q4:R5")
   'Set Rng1 = Me.Range("B7,B12,B17,B22,B28,B40,B45,B50,B55,B60,F7,G45,G50,G60,I7,L4,L9,L14,L23,M59,Q4")
   'Set Rng1 = Me.Range("B7, B12, B17, B22, B28, B40, B45, B50, B55, B60, F7, G45, G50, G60, I7, L4, L9, L14, L23, M59, Q4")
    
    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.Visible = True
    Else
       EMAIL.Visible = False
    End If
End Sub

Would you mind if i sent you my file via your email so you can see where i am going wrong... Or are you getting fed up helping me, i wouldnt blame you, lol

Cheers for all your help

 

[Domenic]

"Demonic" has a somewhat different meaning than "Domenic", but that's okay...

Send me your file and I'll see if I can pinpoint the problem. I'm not sure I'll get a chance to look at it today, though.

 

[Domenic]

The problem seems to lie in the fact that your references are not correct. For example, B7:D8 are merged. So you'll need to refer to B7, not B7:D8. Also, other references point to cells that are empty and, from what I can see, do not ever expect a value. For example, you reference D4 which is located within merged cells and which will always be empty. Try correcting these references and see if it helps.

 

[SyCoDeath]

Domenic,

Thanks... I have noticed that if i add the same ranges in Rng1 & Rng2 it works perfectly.

Problem being the Ranges are on 2 separate Worksheets.

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

Refers to Worksheet "RACECARD ACTIVITY REQUEST"

Where as

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

Should refer to Worksheet "COMPLETE RACECARD TEMPLATE"

I bet this is starting to grate on you now...

Thanks for all your help.

 

[Domenic]

Since the ranges are in two separate worksheets, the code will need to be amended. However, will you always to referencing the same cells in sheet "COMPLETE RACECARD TEMPLATE"?