dynamic textbox input in VBA

Question

my workbook has two sheets, one which contains data and calculations, these data have been name using the name editor function. the second worksheet has a graph and a box consisting of a group of text boxes created using VBA control Active X option, this group of text boxes is to be imputted with values previously named abbove, below is a version of the code I usued, where label and TextBox are the neames of the text boxes and the other names the names of the defined cells. This code reports a 438 message error and would not input the labeled data in the box at the wanted location. How could I make it work so that the textboxes would display the named cells they are linked too?:

Private Sub Worksheet_Activate()
Dim sType As String
Dim sUnit As String
Dim sWellname As String
Dim sDate As String
Dim sMD As String
Dim sTVD As String
Dim sMud As String
Dim sPressure As String
Dim sEMW As String

    sType = ThisWorkbook.Worksheets("sheet1").cbztest.Value
    sUnit = ThisWorkbook.Worksheets("sheet1").cbzPressure.Value
    sWname = ThisWorkbook.Worksheets("sheet1").Range("Wname").Value
    sDate = ThisWorkbook.Worksheets("sheet1").Range("date").Value
    sMD = Format(ThisWorkbook.Worksheets("sheet1").Range("MD").Value, "Standard")
    sTVD = Format(ThisWorkbook.Worksheets("sheet1").Range("TVD").Value, "Standard")
    sMW = ThisWorkbook.Worksheets("sheet1").Range("M_W").Value
    sPressure = Round(ThisWorkbook.Worksheets("sheet1").Range("P_bar").Value, 1)
    sEMW = Format(ThisWorkbook.Worksheets("sheet1").Range("EMW").Value, "Standard")

    sType = ThisWorkbook.Worksheets("sheet1").cbztest.Value
    ActiveSheet("Label").Caption = sType
    ActiveSheet("TextBox1").Text = sWname
    ActiveSheet("TextBox2").Text = sDate
    ActiveSheet("TextBox5").Text = sMD
    ActiveSheet("TextBox6").Text = sTVD
    ActiveSheet("TextBox7").Text = sMW
    ActiveSheet("TextBox8").Text = sPressure
    ActiveSheet("TextBox9").Text = sEMW
    ActiveSheet("Label8").Caption = sType & " EMW :"
    ActiveSheet("Label13").Caption = sUnit

End Sub

Where does the error occur? Have you tried breakpoints / stepping through the code, etc? — Skip Intro
– Skip Intro, Commented Jul 2, 2013 at 13:56
Is this ActiveSheet("Label").Caption = sType correct? Your other Label references have numbers. — Skip Intro
– Skip Intro, Commented Jul 2, 2013 at 13:59
at Label 1 and all the below lines I get the same error 438 and do not know how to solve it — user2453446
– user2453446, Commented Jul 2, 2013 at 14:01
yes it is, even if I put this line in comment I still get the same error message for the one below. I am using the right inputing method? — user2453446
– user2453446, Commented Jul 2, 2013 at 14:02
what are .cbztest. and .cbzPressure.? I think you may be getting the error as the Range class isnt properly overriden — user2140173
– user2140173, Commented Jul 2, 2013 at 14:51

barrowc · Accepted Answer · 2013-07-03 00:36:18Z

1

Try ActiveSheet.TextBox1.Text = sWname and so on

answered Jul 3, 2013 at 0:36

barrowc

10.7k2 gold badges47 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

dynamic textbox input in VBA

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related