Hide form control button when value not met

[ABabeNChrist]

I am using a form control button named (Email_Button) that is invisible when cell B3 is empty and visible when data is entered.
Here how my code appears at the present moment.

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$B$2" Then
        ActiveSheet.Shapes("Email_Button").Visible = Not Target.Value = ""
    End If
End Sub

I would like to expand so that cells B2:B4 all need to have data in order for button to be visible
Also B2:B4 are linked to another sheet so even though cell value may be empty cell still will have a 0
B2 (well be used to enter clients name)
B3 (well have email address)
B4 (well be used for comments)
One problem I did notice was when using a Worksheet_Change it doesn’t function unless I tab out of selected cell

Updated:I even tried using a toogle method

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
    If Sheets("Sheet1").Range("B2,B3,B4").Value = "" Then
        ActiveSheet.Shapes("Email_Button").Visible = False
    Else
        ActiveSheet.Shapes("Email_Button").Visible = True
    End If

End Sub

[HansV]

1) If B2:B4 contain formulas that refer to another worksheet, the Worksheet_Change event will not fire when their value changes - this event only occurs when the user changes a cell directly, not when the result of a formula changes.
You could use the Worksheet_Change event of the worksheet(s) that contain the client name, e-mail address and comments.
Alternatively, you could use the Calculate event of the worksheet that contains the formulas. The Calculate event occurs each time a formula on the worksheet is recalculated.

2)To avoid the problem of a formula returning 0 if the cell it refers to is blank, you can use formulas like this:

=IF(Sheet2!D12="","",Sheet2!D12)

3) You shouldn't use Sheets("Sheet1").Range("B2,B3,B4").Value but count the number of blank values.

For example:

Code: Select all

Private Sub Worksheet_Calculate()
  Shapes("Email_Button").Visible = (Application.CountIf(Range("B2:B4"), "") = 0)
End Sub

[ABabeNChrist]

Hi Hans
I’m was a problem at first with the formula =IF(Sheet2!D12="","",Sheet2!D12)
I’m still really new to formulas
I believe I understand that this formula goes into each of the target cells of sheet1 B2, B3, B4
And that I need to change the D12 in the formula to reflect the cell source for that selected cell
So that when I first entered the formula =IF(Sheet2!B1="","",Sheet2!B1) into my target cell that reflected my source cell
I was not getting a link connection, I then added $ within the formula and it started working, like so
=IF(Sheet2!$B$1="","",Sheet2!$B$1)

[HansV]

The $ characters make the cell reference absolute, i.e. if you fill the formula down to the cells below, each of the formulas will refer to cell B1 on Sheet1, instead of being adjusted automatically. But in the cell where you start, there is no difference whatsoever - at least, there shouldn't be - between the formulas

=IF(Sheet2!B1="","",Sheet2!B1)

and

=IF(Sheet2!$B$1="","",Sheet2!$B$1)

If the former didn't work, you must have done something not entirely correct.

[ABabeNChrist]

The $ characters make the cell reference absolute

I knew your formula was referring to the source on sheet2, and I remembered somewhere about using $ as an absolute. So that’s why I gave it a try. I not sure why it didn’t work at first, but I am going to, further investigate.

if you fill the formula down to the cells below, each of the formulas will refer to cell B1 on Sheet1, instead of being adjusted automatically. But in the cell where you start, there is no difference whatsoever - at least, there shouldn't be - between the formulas

Each of the target cells on sheet1 below have a different formula that reflects the cell source from sheet2
Like so
This was entered into sheet1 B2 =IF(Sheet2!$B$1="","",Sheet2!$B$1)
This was entered into sheet1 B3 =IF(Sheet2!$B$2="","",Sheet2!$B$2)
This was entered into sheet1 B4 =IF(Sheet2!$B$3="","",Sheet2!$B$3)

[HansV]

There are several ways to enter these formulas efficiently, without having to type each of them individually. You can choose whichever of these you like best:

1) Select cell B2 on Sheet1.
Type the formula =IF(Sheet2!B1="","",Sheet2!B1)
Confirm the formula by clicking the green check mark in the formula bar.
Cell B2 should still be selected now.
Press Ctrl+C to copy cell B2.
Select B3:B4.
Press Ctrl+V to paste the formula - it will be adjusted automatically.

2) Select cell B2 on Sheet1.
Type the formula =IF(Sheet2!B1="","",Sheet2!B1)
Without confirming the formula, move the mouse pointer to the little black square in the lower right corner of cell B2.
The cell pointer will change from a cross with white interior to a smaller black cross.
Hold down the left mouse button and drag down to B4, then release the mouse button.

3) Select B2:B4; cell B2 should be the active cell within the selection.
Type the formula =IF(Sheet2!B1="","",Sheet2!B1)
Confirm by pressing Ctrl+Enter.
This will enter the formula in all three cells, adjusting it automatically.

4) Select cell B2 on Sheet1.
Type the formula =IF(Sheet2!B1="","",Sheet2!B1)
Confirm the formula by clicking the green check mark in the formula bar.
Cell B2 should still be selected now.
Select B2:B4.
Press Ctrl+D to fill down the formula - it will be adjusted automatically.

[ABabeNChrist]

There always seems to be more than one way to skin a cat
Of course a figure of speech……

Thank you Hans