How to make textbox macro variable?

norhagan

New Member
Joined
Mar 10, 2009
Messages
4
Below is my code to link and display values from "avg" sheet to a Text Box's 1-6 at D.I. sheet chart. However I want to make values of x,y,z,x1,y1,z1 variables or can be assigned another variables that not contain open/close quotation mark. In order to customize what rows and column from "avg" sheet to display to a Text Box 1-6. You know if properties object containing quotation mark it became a string even if it is declared as integer or long.
If I change the x="avg!a68" to a Sheets("avg").Cells(68,1).Value so that rows and column became cells or assignable with variable, but it did not work.

Thanks....


Sub Update()

Dim x As String
Dim y As String
Dim z As String

x = "avg!a68"
y = "avg!a69"
z = "avg!a70"
x1 = "avg!bo68"
y1 = "avg!bo69"
z1 = "avg!bo70"

Sheets("D.I.").Select
ActiveChart.Shapes("Text Box 1").Select
Selection.Formula = x
ActiveChart.Shapes("Text Box 2").Select
Selection.Formula = y
ActiveChart.Shapes("Text Box 3").Select
Selection.Formula = z
ActiveChart.Shapes("Text Box 4").Select
Selection.Formula = x1
ActiveChart.Shapes("Text Box 5").Select
Selection.Formula = y1
ActiveChart.Shapes("Text Box 6").Select
Selection.Formula = z1
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

pbornemeier

Well-known Member
Joined
May 24, 2005
Messages
3,890
I am not entirely sure what you are trying to do, but I believe this may help.
The first If...End If block in this code will change the text in a text box on one sheet when a particular cell on the activesheet is changed.

To update the other text boxes, add more If..End If blocks within the
Rich (BB code):
Private Sub Worksheet_Change(ByVal Target As Range)
    'This code should be on the code page of the sheet that holds
    'the cells that are being changed
    
    '1st text box
    If Not Intersect(Target, Range("A21")) Is Nothing Then
        With Sheets("D.I.").Shapes("Text Box 1").TextFrame
            .Characters.Text = Range("A21").Value
        End With
        Range("A21").Select
    End If
    
    '2nd text box
    If Not Intersect(Target, Range("B21")) Is Nothing Then
        With Sheets("D.I.").Shapes("Text Box 2").TextFrame
            .Characters.Text = Range("B21").Value
        End With
        Range("B21").Select
    End If
    
End Sub
I don't know of a way to put a formula in a textbox and have it automatically update
 

norhagan

New Member
Joined
Mar 10, 2009
Messages
4
Thanks Phil...what I need is to have another macro code to make textbox variable. Instead of x="avg!A68" which is the code that link to Text Box 1 to display string values from Sheets("avg").Cells(68,1).Value.
"avg!A68"=Sheets("avg").Cells(68,1).Value However if I use Sheets("avg").Cells(68,1).Value it doesn't work or display the value in the Text Box 1. "avg!A68" is a string value. "A" is a Column and "68" is a Row in a Sheet "avg".
 

pbornemeier

Well-known Member
Joined
May 24, 2005
Messages
3,890
If you put this code on the code sheet for the 'avg' worksheet (right-click on the tab and select 'View Code') then manually or programatically change the value of A68 on that sheet, then that cell's value, or the results of that cell's formula, will appear in TextBox1 on the 'D.I.' chart.
Rich (BB code):
Private Sub Worksheet_Change(ByVal Target As Range)
    'This code should be on the code page of the sheet 'avg'    
    '1st text box
    If Not Intersect(Target, Range("A68")) Is Nothing Then
        With Sheets("D.I.").Shapes("Text Box 1").TextFrame
            .Characters.Text = Range("A68").Value
        End With
    End If
End Sub

As far as I know there is no way to have a TextBox value update without VBA; there is no property of the text box that allows for dynamic changing of its contents.
 

norhagan

New Member
Joined
Mar 10, 2009
Messages
4

ADVERTISEMENT

Below is the complete macro code...You will notice that 68,69,70 is redundant, those are the rows that I'm going to change each day just to display the data from sheet 'avg' into the textbox of the sheets chart 'D.I.', 'S.I.' and etc...What I need is to make the rows 68,69,70 to be replaced with variables that means open/close quotation mark "" property. I tried already the Eq-2 replaced with Eq-1 but didn't work, it remarks 'Unable to set the Formula property of the TextBox class'...I hope their is still another technique to make the textbox property be a variable...



Sub Update()
'
' Update Macro
' Macro recorded 2/19/2009 by Norman H. Ganancial
'
'
Dim x As String
Dim y As String
Dim z As String

'x = Sheets("avg").Cells(64, 1).Value '...Equation-1

x = "avg!a68" 'Equation-2
y = "avg!a69"
z = "avg!a70"

Sheets("D.I.").Select
ActiveChart.Shapes("Text Box 1").Select
Selection.Formula = x
ActiveChart.Shapes("Text Box 2").Select
Selection.Formula = y
ActiveChart.Shapes("Text Box 3").Select
Selection.Formula = z
ActiveChart.Shapes("Text Box 4").Select
Selection.Formula = "avg!bo68"
ActiveChart.Shapes("Text Box 5").Select
Selection.Formula = "avg!bo69"
ActiveChart.Shapes("Text Box 6").Select
Selection.Formula = "avg!bo70"

Sheets("S.I.new").Select
ActiveChart.Shapes("Text Box 2").Select
Selection.Formula = x
ActiveChart.Shapes("Text Box 3").Select
Selection.Formula = y
ActiveChart.Shapes("Text Box 4").Select
Selection.Formula = z
ActiveChart.Shapes("Text Box 5").Select
Selection.Formula = "avg!bn68"
ActiveChart.Shapes("Text Box 6").Select
Selection.Formula = "avg!bn69"
ActiveChart.Shapes("Text Box 7").Select
Selection.Formula = "avg!bn70"

Sheets("-5").Select
ActiveChart.Shapes("Text Box 410").Select
Selection.Formula = x
ActiveChart.Shapes("Text Box 411").Select
Selection.Formula = y
ActiveChart.Shapes("Text Box 412").Select
Selection.Formula = z
ActiveChart.Shapes("Text Box 413").Select
Selection.Formula = "avg!bj68"
ActiveChart.Shapes("Text Box 414").Select
Selection.Formula = "avg!bj69"
ActiveChart.Shapes("Text Box 415").Select
Selection.Formula = "avg!bj70"

Sheets("+5").Select
ActiveChart.Shapes("Text Box 12").Select
Selection.Formula = x
ActiveChart.Shapes("Text Box 13").Select
Selection.Formula = y
ActiveChart.Shapes("Text Box 14").Select
Selection.Formula = z
ActiveChart.Shapes("Text Box 15").Select
Selection.Formula = "avg!bi68"
ActiveChart.Shapes("Text Box 16").Select
Selection.Formula = "avg!bi69"
ActiveChart.Shapes("Text Box 17").Select
Selection.Formula = "avg!bi70"

Sheets("+10").Select
ActiveChart.Shapes("Text Box 400").Select
Selection.Formula = x
ActiveChart.Shapes("Text Box 401").Select
Selection.Formula = y
ActiveChart.Shapes("Text Box 402").Select
Selection.Formula = z
ActiveChart.Shapes("Text Box 403").Select
Selection.Formula = "avg!bh68"
ActiveChart.Shapes("Text Box 404").Select
Selection.Formula = "avg!bh69"
ActiveChart.Shapes("Text Box 405").Select
Selection.Formula = "avg!bh70"

Sheets("+50").Select
ActiveChart.Shapes("Text Box 391").Select
Selection.Formula = x
ActiveChart.Shapes("Text Box 392").Select
Selection.Formula = y
ActiveChart.Shapes("Text Box 393").Select
Selection.Formula = z
ActiveChart.Shapes("Text Box 394").Select
Selection.Formula = "avg!bg68"
ActiveChart.Shapes("Text Box 395").Select
Selection.Formula = "avg!bg69"
ActiveChart.Shapes("Text Box 396").Select
Selection.Formula = "avg!bg70"

Sheets("MMS").Select
ActiveChart.Shapes("Text Box 1").Select
Selection.Formula = x
ActiveChart.Shapes("Text Box 2").Select
Selection.Formula = y
ActiveChart.Shapes("Text Box 3").Select
Selection.Formula = z
ActiveChart.Shapes("Text Box 4").Select
Selection.Formula = "avg!bl68"
ActiveChart.Shapes("Text Box 5").Select
Selection.Formula = "avg!bl69"
ActiveChart.Shapes("Text Box 6").Select
Selection.Formula = "avg!bl70"

Sheets("LSF Size").Select
ActiveChart.Shapes("Text Box 1559").Select
Selection.Formula = x
ActiveChart.Shapes("Text Box 1560").Select
Selection.Formula = y
ActiveChart.Shapes("Text Box 1561").Select
Selection.Formula = z
ActiveChart.Shapes("Text Box 1562").Select
Selection.Formula = "avg!af68"
ActiveChart.Shapes("Text Box 1563").Select
Selection.Formula = "avg!af69"
ActiveChart.Shapes("Text Box 1564").Select
Selection.Formula = "avg!af70"

Sheets("FeO").Select
ActiveChart.Shapes("Text Box 1").Select
Selection.Formula = x
ActiveChart.Shapes("Text Box 2").Select
Selection.Formula = y
ActiveChart.Shapes("Text Box 3").Select
Selection.Formula = z
ActiveChart.Shapes("Text Box 4").Select
Selection.Formula = "avg!bt68"
ActiveChart.Shapes("Text Box 5").Select
Selection.Formula = "avg!bt69"
ActiveChart.Shapes("Text Box 6").Select
Selection.Formula = "avg!bt70"

Sheets("CaO").Select
ActiveChart.Shapes("Text Box 973").Select
Selection.Formula = x
ActiveChart.Shapes("Text Box 974").Select
Selection.Formula = y
ActiveChart.Shapes("Text Box 975").Select
Selection.Formula = z
ActiveChart.Shapes("Text Box 976").Select
Selection.Formula = "avg!ca68"
ActiveChart.Shapes("Text Box 977").Select
Selection.Formula = "avg!ca69"
ActiveChart.Shapes("Text Box 978").Select
Selection.Formula = "avg!ca70"

End Sub
 

pbornemeier

Well-known Member
Joined
May 24, 2005
Messages
3,890
According to the documentation, the formula for a text box can only reference a single cell, either by specifying the address of the cell or by defining and using a name that refers to a single cell.

So have each of your text boxes pointing at a different cell then use your macro to populate the appropriate cells with the desired formula whose result will be passed on to the referencing text box.
 

norhagan

New Member
Joined
Mar 10, 2009
Messages
4
Thanks Phil...anyway for the main time I'll continue manually change the rows number either at the textbox field link or at the code formula. However this workbook is still not a user's friendly and subject to error if somebody use it and accidentally erase or mis-encode the right values.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,447
Messages
5,642,209
Members
417,262
Latest member
andrewd1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top