PDA

View Full Version : Controlling the display of a text box



Joy Cooper
09-09-2010, 03:33 AM
I'm wondering if there's a way of controlling the display of a text box in a worksheet, when the value of the cell G25 = 0.

I would prefer to use a text box as opposed to a User Form as I need the text box to remain in view whilst the user continues to navigate around the worksheet. I've noticed that when a user form is in view the worksheet becomes inaccessible.

Thank you.

GTO
09-09-2010, 03:35 AM
Greetings Joy,

What do you mean by 'controlling the display'?

Mark

Joy Cooper
09-09-2010, 03:40 AM
What I'm trying to do is literally turn the visibility of the text box on and off. i.e.

=If(G25=0, "Display text box", hide) or in VBA speak

If Range("G25").value = 0 Then
textbox.show
End if

This would work for a userform, but there appears to be no way of applying a name to a text box from the "Drawing" toolbar.

Thank you.

GTO
09-09-2010, 04:01 AM
I'm not very good with Shapes, but see if either of the following help at all.


Option Explicit

Sub HideTBox()
Dim TBox As Shape

Set TBox = Me.Shapes("Text Box 1")

If Range("G25").Value = 0 And Not Range("G25").Value = vbNullString Then
TBox.Visible = msoTrue
Else
TBox.Visible = msoFalse
End If

End Sub

Sub HideTextBox()
Dim TBox As Shape

Set TBox = Me.Shapes("Text Box 1")

TBox.Visible = TBox.Visible = msoFalse
End Sub

Joy Cooper
09-09-2010, 06:06 AM
Thank you for responding.

I've tried both methods by entering the code (seperately) into a module and then assigning a macro to the text box, but regrettably, neither seemed to do the trick.

I'm relatively new to VBA - am I right in saying that this code should be in a Module?

Joy Cooper
09-09-2010, 06:07 AM
Sorry I should also have said, I tried in the Sheet window as well.

GTO
09-09-2010, 07:22 AM
Thank you for responding.

I've tried both methods by entering the code (seperately) into a module and then assigning a macro to the text box, but regrettably, neither seemed to do the trick.

I'm relatively new to VBA - am I right in saying that this code should be in a Module?

Hi Joy,

Yes, all code goes in a module of some sort. For starting out, I would first mention what one could think of as three different types of modules.

For general code, where you might let's say, have a button that fires it, there are Standard Module(s). While in VBIDE (from any sheet, ALT + F8), from the menubar, Insert|Module (not Class Module).

For stuff that you want to happen automatically, such as when G25 on a particular sheet changes to zero, there is the sheet's module, or the ThisWorkbook module. For this post, we'll just use the worksheet's module.

When you right-click a worksheet's tab and select View Code, that takes you to that sheet's module. There you can use certain events such as the Worksheet_Change event to "catch" when a cell or cells has value(s) change due to code running or input from the user, and you can use this to do stuff you want to happen when, for instance, show or hide your (shapes) textbox.

There is also a Worksheet_Calculate event that occurs whenever the sheet is calculated. When a cell's returned value is changed as a result of a formula on the sheet, this is the one to use.

So... is G25 changing to a 0 as the result of your typing in a zero, or as the result of a formula?

If it is the result of a formula, try:


Private Sub Worksheet_Calculate()
Dim TBox As Shape

Set TBox = Me.Shapes("Text Box 1")

If Range("G25").Value = 0 And Not Range("G25").Value = vbNullString Then
TBox.Visible = msoTrue
Else
TBox.Visible = msoFalse
End If
End Sub

Hope that helps,

Mark

GTO
09-09-2010, 07:38 AM
A "reverse" bump? For some reason, the site went off for just a second; picked up my post, but didn't update in the forum page...

Joy Cooper
09-09-2010, 07:57 AM
Yes that does help thank you and by trial and error have got in working. In response to your query regarding the content of G25, this is a formula which changes based on the contents of other cells.

I'm actually creating a balanced scorecard for multiple users so the content of G25 will changed based on the selection from drop down boxes that the user chooses.

Many thanks for you help. Problem now solved.