Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filling a WS from a UserForm
I am using a UF to fill a WS with unit count and product. For example, 2
Widgets, 2 Gadgets, 2 Gidgets should fill as follows: 1 Widget 2 Widget 3 Gadget 4 Gadget 5 Gidget 6 Gidget The following code works: Private Sub CommandButton1_Click() Dim myCell As Range Set myCell = ActiveSheet.Range("A1") Dim j As Long, k As Long, i As Long ActiveSheet.Range("A1:B15").Value = "" On Error Resume Next j = 0 For k = 1 To 10 Step 2 For i = 1 To CLng(UserForm1.Controls("TB" & k).Text) j = j + 1 myCell.Offset(j - 1, 0).Value = j myCell.Offset(j - 1, 1).Value = _ UserForm1.Controls("TB" & k + 1).Text Next i Next k myCell.Select On Error GoTo 0 End Sub However, my UF allows a unit count for up to 5 products. So when I enter only the 3 products (Widget, Gadget, Gidget) the WS looks like this: 1 Widget 2 Widget 3 Gadget 4 Gadget 5 Gidget 6 Gidget 7 8 How can I correct the code to only count the 3 products entered? Also (if I'm not pushing my luck), I would prefer to use a TB for the unit count only, and a list box for the product. If a code can be written for that, I would very much help with that also. Thank you in advance. Jim Kobzeff |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filling a WS from a UserForm
Maybe...
Option Explicit Private Sub CommandButton1_Click() Dim myCell As Range Set myCell = ActiveSheet.Range("A1") Dim j As Long, k As Long, i As Long ActiveSheet.Range("A1:B15").Value = "" On Error Resume Next j = 0 For k = 1 To 10 Step 2 For i = 1 To CLng(UserForm1.Controls("TB" & k).Text) If Trim(UserForm1.Controls("TB" & k + 1).Text) = "" Then 'do nothing Else j = j + 1 myCell.Offset(j - 1, 0).Value = j myCell.Offset(j - 1, 1).Value = _ UserForm1.Controls("TB" & k + 1).Text End If Next i Next k myCell.Select On Error GoTo 0 End Sub I'm not sure why you're using the "on error" stuff. I don't see anything that can blow it up real good. ======== As an alternative, have you thought about just having 3 textboxes for the quantities--but have the widgets, gadgets, and gidgets as labels on your user form--or even comboboxes that support 0 to 5 for each item. Kind of like: Option Explicit Private Sub CommandButton1_Click() Dim myCell As Range Set myCell = ActiveSheet.Range("A1") Dim CtrlCtr As Long Dim QtyCtr As Long Dim RowCtr As Long ActiveSheet.Range("A1:B15").Value = "" RowCtr = 0 For CtrlCtr = 1 To 3 For QtyCtr = 1 To Val(Me.Controls("combobox" & CtrlCtr).Value) myCell.Offset(RowCtr, 0).Value = RowCtr + 1 myCell.Offset(RowCtr, 1).Value = Me.Controls("label" & CtrlCtr).Caption RowCtr = RowCtr + 1 Next QtyCtr Next CtrlCtr myCell.Select End Sub Private Sub UserForm_Initialize() Dim CtrlCtr As Long Dim QtyCtr As Long For CtrlCtr = 1 To 3 With Me.Controls("combobox" & CtrlCtr) .Style = fmStyleDropDownList For QtyCtr = 0 To 5 .AddItem QtyCtr Next QtyCtr End With Next CtrlCtr End Sub JK wrote: I am using a UF to fill a WS with unit count and product. For example, 2 Widgets, 2 Gadgets, 2 Gidgets should fill as follows: 1 Widget 2 Widget 3 Gadget 4 Gadget 5 Gidget 6 Gidget The following code works: Private Sub CommandButton1_Click() Dim myCell As Range Set myCell = ActiveSheet.Range("A1") Dim j As Long, k As Long, i As Long ActiveSheet.Range("A1:B15").Value = "" On Error Resume Next j = 0 For k = 1 To 10 Step 2 For i = 1 To CLng(UserForm1.Controls("TB" & k).Text) j = j + 1 myCell.Offset(j - 1, 0).Value = j myCell.Offset(j - 1, 1).Value = _ UserForm1.Controls("TB" & k + 1).Text Next i Next k myCell.Select On Error GoTo 0 End Sub However, my UF allows a unit count for up to 5 products. So when I enter only the 3 products (Widget, Gadget, Gidget) the WS looks like this: 1 Widget 2 Widget 3 Gadget 4 Gadget 5 Gidget 6 Gidget 7 8 How can I correct the code to only count the 3 products entered? Also (if I'm not pushing my luck), I would prefer to use a TB for the unit count only, and a list box for the product. If a code can be written for that, I would very much help with that also. Thank you in advance. Jim Kobzeff -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
filling userform textboxes | Excel Programming | |||
filling information from one cell and filling another. | Excel Worksheet Functions | |||
Activating userform and filling it with data form row where userform is activate | Excel Programming | |||
Using Esc Botton while filling in userform | Excel Programming | |||
Filling Userform in Word with Excel cell value. | Excel Programming |