Daft Textbox question...?

[lee007uk]

hello programmer types...
how do u pull the information from a textbox??

i thought it would be really easy, i managed to do all sorts of things but not pull information from a textbox and use it.

Sorry for asking such a easy question but the help file don't tell me (well i can find it...) and nothing on any forums...

Cheers Guy and Gals

 

[JohnAcc]

Try returning the data from your text box by storing it in a variable...

myvariable = textbox1.text

Let me know how you get on..........

Cheers

John

 

[lee007uk]

i try that and it comes up with :

Run-time error '424'

Object required

i thought it would be easy, but i'm sure i'm missing something really obivous....

i'm using excel VBA, does that make a difference?

 

[Bowers74]

Dim MyVariable as String
MyVariable = TextBox1.Value

The variable needs to be declared before you can give it a value.

What program are you using anyway, and where is the textbox value supposed to be going?



Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[JohnAcc]

The variable doesn't need to be declared b4 you assign a value to it because vba will automatically assign the variable as a variant type. ( unless you have an option explicit statement )

It is obviously more efficient to declare the variable b4 using it.

 

[lee007uk]

i've done as u said and still get the same error...
it's just normal Excel 2000....

The textbox is used to specify the directory where a file is being saved, the name of the file is automatically created

all the automatic creating of file name and organising information works fine, it's just this last bit!

grrr!

been messing about for a couple of days and getting bored of it now!!

The help files are rubbish, and noone seems to do this, only help i find is how to put information from VB into a textbox...not the other way around....is there a better way of doing this?

little note my boss will want to do as little work as possible when it comes to using this thing (program...)i'm writing...

 

[VBAva]

Where did you declare the variable?
does it need to be public?
where is it referenced from?

 

[Bowers74]

Could you show us a copy of your code so that we can get a better idea as to what it is you want to do?



Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[lee007uk]

right here's me code....
it's a bit messy as it's one of the first ones i've done, i declare at the top CoilQDir and SaveDir, then just below i try and move the textbox information in the variables....

hope this makes sense to someone!

Cheers guys

Sub Load_Spec()
'
' Load_Spec Macro
' Macro created by Lee Howard
'
'
Dim MyTime ' Return current system time
Dim SaveName As String ' Name of File to be saved
Dim JobNumber As String ' Current Job Number
Dim CoilQDir As String
Dim SaveDir As String

MyTime = Time
CoilQDir = Me.TextBox2.Value
SaveDir = Me.TextBox1.Value

' Open Spec File created by CoilQ

Workbooks.OpenText FileName:=CoilQDir & "SPEC", Origin:=xlWindows, StartRow _
:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False _
, Space:=False, Other:=False, FieldInfo:=Array(1, 1)

JobNumber = Worksheets(1).Range("A1&quot ' Specify job number
SaveName = SaveDir & JobNumber & ".xls " ' Specify file name to save

' Check if the current CoilQ job exists

If Dir(SaveDir & JobNumber & ".xls &quot = "" Then

' File does not exits so create a new file

Workbooks.Open FileName:=CoilQDir & "SPEC" ' Activate spec file
Columns("A:A&quot.Select ' Select Column A
Selection.Insert Shift:=xlToRight ' Insert a new column and shift everything right

' After inserting a new column enter all of the title for the CoilQ data

Range("A1&quot.Select
ActiveCell.FormulaR1C1 = "Code Name"
Range("A2&quot.Select
ActiveCell.FormulaR1C1 = "Reference Number"
Range("A3&quot.Select
ActiveCell.FormulaR1C1 = "Date"
Range("A4&quot.Select
ActiveCell.FormulaR1C1 = "Coil Length"
Range("A5&quot.Select
ActiveCell.FormulaR1C1 = "Mandril Size"
Range("A6&quot.Select
ActiveCell.FormulaR1C1 = "Copper Width"
Range("A7&quot.Select
ActiveCell.FormulaR1C1 = "Copper Thickness"
Range("A8&quot.Select
ActiveCell.FormulaR1C1 = "Waterway Width"
Range("A9&quot.Select
ActiveCell.FormulaR1C1 = "Waterway Depth"
Range("A10&quot.Select
ActiveCell.FormulaR1C1 = "Insulation/face"
Range("A11&quot.Select
ActiveCell.FormulaR1C1 = "Space Thickness"
Range("A12&quot.Select
ActiveCell.FormulaR1C1 = "Number of Layers"
Range("A13&quot.Select
ActiveCell.FormulaR1C1 = "Section Reference"
Range("A14&quot.Select
ActiveCell.FormulaR1C1 = "Diameter"
Range("A15&quot.Select
ActiveCell.FormulaR1C1 = "Length"
Range("A16&quot.Select
ActiveCell.FormulaR1C1 = "Resistivity"
Range("A17&quot.Select
ActiveCell.FormulaR1C1 = "Permeability"
Range("A18&quot.Select
ActiveCell.FormulaR1C1 = "Start Temp."
Range("A19&quot.Select
ActiveCell.FormulaR1C1 = "Final Temp."
Range("A20&quot.Select
ActiveCell.FormulaR1C1 = "Heat Content"
Range("A21&quot.Select
ActiveCell.FormulaR1C1 = "Density"
Range("A22&quot.Select
ActiveCell.FormulaR1C1 = "Material"
Range("A23&quot.Select
ActiveCell.FormulaR1C1 = "Billets/Hour"
Range("A24&quot.Select
ActiveCell.FormulaR1C1 = "Kilogram/Hour"
Range("A25&quot.Select
ActiveCell.FormulaR1C1 = "Handling Tiem"
Range("A26&quot.Select
ActiveCell.FormulaR1C1 = "Soak Time"
Range("A27&quot.Select
ActiveCell.FormulaR1C1 = "Billets in coil"
Range("A28&quot.Select
ActiveCell.FormulaR1C1 = "Thermal Eff'y"
Range("A29&quot.Select
ActiveCell.FormulaR1C1 = "Frequence"
Range("A30&quot.Select
ActiveCell.FormulaR1C1 = "Phase Voltage"
Range("A31&quot.Select
ActiveCell.FormulaR1C1 = "Number of Phases"
Range("A32&quot.Select
ActiveCell.FormulaR1C1 = "Power Factor"
Range("A33&quot.Select
ActiveCell.FormulaR1C1 = "Coil Efficiency"
Range("A34&quot.Select
ActiveCell.FormulaR1C1 = "Coil Power Factor"
Range("A35&quot.Select
ActiveCell.FormulaR1C1 = "Line Consumption"
Range("A36&quot.Select
ActiveCell.FormulaR1C1 = "Coil Current"
Range("A37&quot.Select
ActiveCell.FormulaR1C1 = "Phase Voltage"
Range("A38&quot.Select
ActiveCell.FormulaR1C1 = "Phase Power"
Range("A39&quot.Select
ActiveCell.FormulaR1C1 = "Phase KVA"
Range("A40&quot.Select
ActiveCell.FormulaR1C1 = "Capacitive KVAR"
Range("A41&quot.Select
ActiveCell.FormulaR1C1 = "Turns/Phase"
Range("A42&quot.Select
ActiveCell.FormulaR1C1 = "Discs/Phase"
Range("A43&quot.Select
ActiveCell.FormulaR1C1 = "Disc Pair Voltage"
Range("A44&quot.Select
ActiveCell.FormulaR1C1 = "Disc Pair Loss"
Range("A45&quot.Select
ActiveCell.FormulaR1C1 = "Disc Pair Flow"
Range("A46&quot.Select
ActiveCell.FormulaR1C1 = "Trans KVA/Phase"
Range("A47&quot.Select
ActiveCell.FormulaR1C1 = "D/P Copper Length"

Rows("4:4&quot.Select ' Select Column 4
Selection.Insert Shift:=xlDown ' Insert a new row and shift everthing down
Range("A4&quot.Select ' select A4
ActiveCell.FormulaR1C1 = "Time" ' Enter the text time
Range("B4&quot.Select ' Select A5
ActiveCell.FormulaR1C1 = Time ' Enter the variant Time
Range("B5&quot.Select

Cells.Select ' Select All
Cells.EntireColumn.AutoFit ' Resize all column and rows to fit new information

' Save the new job

ActiveCell.FormulaR1C1 = JobNumber
ActiveWorkbook.SaveAs FileName:= _
SaveName, FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False

' File does exist so open it and add new data

Else
Workbooks.Open FileName:=SaveDir & JobNumber & ".xls "

Columns("B:B&quot.Select
Selection.Insert Shift:=xlToRight
Workbooks.OpenText FileName:="G:\COILQ\SPEC", Origin:=xlWindows, StartRow _
:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False _
, Space:=False, Other:=False, FieldInfo:=Array(1, 1)
Columns("A:A&quot.Select
Selection.Copy
Windows(JobNumber & ".xls&quot.Activate
Range("B1&quot.Select
ActiveSheet.Paste
Range("B4&quot.Select
Application.CutCopyMode = False
Selection.Insert Shift:=xlDown
Range("B4&quot.Select
ActiveCell.FormulaR1C1 = Time

Cells.Select ' Select All
Cells.EntireColumn.AutoFit ' Resize all column and rows to fit new information


ActiveCell.FormulaR1C1 = JobNumber
ActiveWorkbook.Save

Workbooks("SPEC&quot.Close SaveChanges:=False

End If

End Sub

 

[Bowers74]

Which line are you receiveing the error message on?

Do you have a closing backslash "\" at the end of the text in your textboxes? I have to ask, because they are not hardcoded.





Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[lee007uk]

CoilQDir = Me.TextBox2.Value

is where the program stops

yep got a closing \ on the end of the directory

 

[lee007uk]

i got the solution!!!

you have to specify the sheet and the name of the textbox e.g

MyVariable = Sheets(1).TextBox1.Text

cheers for ur help guys...

any one want to run there eyes over the code and tell me anything else that i've done wrong, or could be done better??

 

[Bowers74]

With the Me. before the TextBoxes, I am sure that we were all assuming that they were on a UserForm.

I'm glad you got it figured out though.



Peace!

Mike

Never say Never!!!
Nothing is impossible!!!