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.
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.
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.
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
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.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.