finaljustice
Board Regular
- Joined
- Oct 6, 2010
- Messages
- 175
Hi,
I have a userform where the user will register types of steel and their chemical composition. I am having issues formatting the data from the userform back into the spreadsheet to be saved. When I populate the cells, they are put as text and not numbers, they must be defined as numbers on those cells. I used Format(#, "Standard") but only when retrieving information to populate textbox and thought it would work.
Such as:
Ergo I tried using it on the way back to the spreadsheet. I also declared the array variable as Variant since the array receives text and numbers, since I wasn't sure of what type of variable deals with numbers and text, I used Variant.
Is there another way to format the data back into the spreadsheet?
On a side note, out of curiosity, is there a way to have the same result as above without the use of the GoTo?
Thank you for your time & attention.
Luis
I have a userform where the user will register types of steel and their chemical composition. I am having issues formatting the data from the userform back into the spreadsheet to be saved. When I populate the cells, they are put as text and not numbers, they must be defined as numbers on those cells. I used Format(#, "Standard") but only when retrieving information to populate textbox and thought it would work.
Such as:
Code:
UserForm1.TextBox1 = Format(ThisWorkbook.Sheets("LD").Range("W28"), "Standard")
Ergo I tried using it on the way back to the spreadsheet. I also declared the array variable as Variant since the array receives text and numbers, since I wasn't sure of what type of variable deals with numbers and text, I used Variant.
Code:
Option Base 1
Public steelVar(35) As Variant
Public UltL As Long
Sub SaveSteel() 'saves the steeel that is about to be registered
Application.ScreenUpdating = False
'get data from userform into a array variable.
steelVar(1) = UserForm3.TextBox59.Value 'this is a text the "name" of the steel
For i = 2 To 35
steelVar(i) = UserForm3.Controls("Textbox" & i + 22) 'this is the data, generally decimals
Next
'find last row occupied.
UltL = ThisWorkbook.Worksheets("Acos").Range("A" & Rows.Count).End(xlUp).Row + 1
'putting info into the sheet.
For i = 1 To 35
If i = 1 Then
ThisWorkbook.Sheets("Acos").Range("A" & UltL).Offset(0, i - 1) = steelVar(i) 'put name of the steel
GoTo SkipName
End If
ThisWorkbook.Sheets("Acos").Range("A" & UltL).Offset(0, i - 1) = Format(steelVar(i), "Standard") 'put data into sheet
SkipName:
Next
'refresh the combobox with the new steel.
UltL = ThisWorkbook.Worksheets("Acos").Range("A" & Rows.Count).End(xlUp).Row - 2
ThisWorkbook.Sheets("Acos").Range("A3:A" & UltL).Name = "list_steel"
UserForm1.ComboBox1.RowSource = "list_steel"
MsgBox "Success!"
UserForm3.Hide
Application.ScreenUpdating = True
End Sub
Is there another way to format the data back into the spreadsheet?
On a side note, out of curiosity, is there a way to have the same result as above without the use of the GoTo?
Thank you for your time & attention.
Luis