Making a textbox visible on meeting a condition...

[cal25]

I have a textbox on my form and want it to be linked to a cell say A7. When B7 < 40 (which is days calculated between 2 dates) the textbox becomes visible…

Seems so frustratingly simple but cannot get it to work... Thanks in advance...

Private Sub TextBox1_Change()

If Cell(A7) > 40 Then
ActiveSheet.Shapes("Textbox1").Visible = False
Else
ActiveSheet.Shapes("Textbox1").Visible = True
End If

End Sub

 

[Peter_SSs]

Are you saying that when B7 changes, you want to set the visibility of the textbox depending on the value of B7? If so what 2 cells does the formula in B7 refer to and are those 2 cells entered manually, by code, or the result of formulas?

 

[cal25]

Sorry... When A7 < 40

 

[cal25]

To make clear what I have already requested. above... I have a textbox on my form and want it to be linked to a cell say A7. When A7 < 40 (which is days calculated between 2 dates) the textbox becomes visible…
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
Private Sub TextBox1_Change()
<o> </o>
If Cell(A7) > 40 Then
Textbox1.Visible = False
Else
Textbox1.Visible = True
End If
<o> </o>
End Sub

 

[Peter_SSs]

.. textbox on my form ..

By 'form' do you mean a vba UserForm or do you just mean your worksheet?

My question from before still stands..
Are you saying that when A7 changes, you want to set the visibility of the textbox depending on the value of A7? If so what 2 cells does the formula in A7 refer to and are those 2 cells entered manually, by code, or the result of formulas?

 

[cal25]

Hi, thanks for your reply. I mean the worksheet when I say form. The 2 cells contain dates, and it is the number of days calculated between the dates contained within cell A7 that determines whether the textbox message is visible or not.

 

[Peter_SSs]

The 2 cells contain dates, and it is the number of days calculated between the dates contained within cell A7 that determines whether the textbox message is visible or not.

You've basically said that three times now and I understand it, but you aren't answering (all) the questions being asked.

.. what 2 cells does the formula in A7 refer to and are those 2 cells entered manually, by code, or the result of formulas?

 

[cal25]

Hope I have got it right this time... apologies

A1 = Today()
H15 = 01/11/2011 …this is a date which specifies when a deadline must be completed (entered manually)
A7 =DATEDIF(A1, H15, "D")
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
If A7 < 40days a textbox will display a warning
<o> </o>

 

[Peter_SSs]

Thank you, try this in a copy of your workbook.

1. Right click the sheet name tab and choose "View Code".

2. Copy and Paste the code below into the main right hand pane that opens at step 1.

<font face=Courier New><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br>    <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> Intersect(Target, Range("H15")) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>        ActiveSheet.TextBox1.Visible = (Range("A7").Value < 40)<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br></FONT>


In the vba window, double click the ThisWorkbook module of your workbook in the left hand pane and paste the following code there. Adjust the sheet name in the code as required.


<font face=Courier New><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Workbook_Open()<br>    <SPAN style="color:#00007F">With</SPAN> Sheets("Sheet1") <SPAN style="color:#007F00">'<- Sheet with Text box</SPAN><br>        .TextBox1.Visible = (.Range("A7").Value < 40)<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

Now when the workbook is opened, the code should check A7 and adjust the visibility of the TextBox. Also, if cell H15 is altered the check should also be performed.

 

[cal25]

Thank you Peter, very much appreciated... It works a dream.

It certainly shows I have a lot to learn in terms of learning to use efficient code..