0

I have a form that takes a user input and multiplies that input by values in another worksheet using a Text Box. I want all the inputs to take place in the same form but for some reason it's printing out 0 for my calculations. Should I use another control for a numerical input?

Global qty1 As Variant
Global qty2 As Variant
'assign qty1 and qty2 as input variables for number of assemblies

Sub PartOrder()

Sheets.Add After:=Worksheets(Sheets.Count) 'Creates new worksheet for part order form

PartOrderForm.Show 'displays the form



End Sub

Private Sub CompleteForm_Click()
If CheckBox1.Value = True Then 'If the first assembly is selected


ActiveSheet.Range("A1") = "Part Number"
ActiveSheet.Range("B1") = "Part Name"
ActiveSheet.Range("C1") = "Number of Parts Needed" 'Column headings

TextBox2.Value = qty1 'quantity of 1st assembly

Range("A2").Activate
For i = 3 To 8 'For loop copies part numbers, names and number of parts needed into new sheet
ActiveSheet.Cells(i - 1, 1) = Worksheets("F8X SUSPENSION LINKS REV2").Cells(4 + i, 2)
ActiveSheet.Cells(i - 1, 2) = Worksheets("F8X SUSPENSION LINKS REV2").Cells(4 + i, 3)
ActiveSheet.Cells(i - 1, 3) = Worksheets("F8X SUSPENSION LINKS REV2").Cells(4 + i, 6) * qty1
Next i


Else: End If

If CheckBox2.Value = True Then 'Repeat code above if Trail link is selected
TextBox3.Value = qty2
For i = 3 To 8
ActiveSheet.Cells(i + 6, 1) = Worksheets("F8X SUSPENSION LINKS REV2").Cells(13 + i, 2)
ActiveSheet.Cells(i + 6, 2) = Worksheets("F8X SUSPENSION LINKS REV2").Cells(13 + i, 3)
ActiveSheet.Cells(i + 6, 3) = Worksheets("F8X SUSPENSION LINKS REV2").Cells(13 + i, 6) * qty2
Next i

Else: End If

ActiveSheet.Range("A1:C1").Columns.AutoFit 'Formatting cells


End Sub
braX
  • 11,506
  • 5
  • 20
  • 33
snt
  • 55
  • 4
  • 3
    Side note: In general you want to [avoid using select and activate](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) in your code. The active sheet is not always the one you think it is. – cybernetic.nomad Aug 11 '22 at 20:05
  • A text box always contains plain text after the user has entered it. Even if numbers are entered. Everything is a string. Therefore, this is one of the places in VBA where active typecasting must be performed. Typecasting changes the data type of a value. For example, to cast the string `"100"` to an integer of type Long, the method `CLng()` is used: `CLng("100")`. For floating point values `CDbl()` is used and there are more methods, for other data types. So you have to use `CLng(qty1)`, or `CDbl(qty1)`. – Zwenn Aug 12 '22 at 13:17
  • Do not give the variables the data type Variant, but the target data type. You have to include an error check anyway, because it is uncertain whether the user really enters a number. This must be caught. Look here for typecasting: https://learn.microsoft.com/en-us/office/vba/language/concepts/getting-started/type-conversion-functions – Zwenn Aug 12 '22 at 13:17

0 Answers0