PDA

View Full Version : vb script for userform



Emoncada
02-21-2008, 02:05 PM
I have this script that worked great with form created on a spreadsheet. Now I would like to use the same script but for a userform.


Sub Button17()
Dim y, x As Integer
y = InputBox("How Many?")
If y = "" Then
Exit Sub
Else
x = 0
Do Until x = y
myList = "HP 17IN. TFT L1706"
Call Bundle_Main
x = x + 1
Loop
End If
End Sub


Sub Bundle_Main()
Dim r As Range, rngData As Range, t As Range, rngAmount As Range
Dim myNum As Long, i As Long, p As Long
'split the myList variable into an array--comma delimited
'and then you can loop through it.
myList = Split(myList, ",")
Set rngData = Range("C242:C331")
Set r = rngData.Find("*", SearchDirection:=xlPrevious)
If r Is Nothing Then
Set r = rngData(1)
ElseIf r.Row >= 331 Then
MsgBox "Not enough room"
Exit Sub
Else
Set r = r.Offset(1)
End If

myNum = WorksheetFunction.CountIf(rngData, myList(0))
If myNum > 0 Then
With rngData
Set r = .Find(what:=myList(0), After:=.Cells(.Cells.Count), _
LookAt:=xlWhole, SearchDirection:=xlPrevious).Offset(1)
End With
If r.Row >= 331 Then
MsgBox "Not enough room"
Exit Sub
End If
End If
For i = LBound(myList) To UBound(myList)
With r
.Offset(i, 0) = myList(i)
.Offset(i, 1) = myNum + 1
End With
Next i
r.Offset(, 1) = 1
End Sub

Basically what I think would need to be changed is the "Range"
Instead of cells ("C242:C331") I need "CmbBoxDesc1 - CmbBoxDesc54"

How can I make this work?

Emoncada
02-22-2008, 02:42 PM
BUMP!

mdmackillop
02-23-2008, 05:11 AM
Can you post some sample data?

Emoncada
03-05-2008, 07:15 AM
Basically I have Txtboxes that get filled either via Dropdown or by a cmdbutton. I would like for when the user clicks the
cmdbutton say "HP NC8430" it will prompt with a txtbox or form asking how many needed? if they type 10 then it will look at the range of txtboxes TxtDesc1 - TxtDesc54 and start with the first one If TxtDesc.Value = "" Then then drop the "HP NC8430" in TxtDesc1 : TxtDesc10. and TxtQua1 : TxtQua10 would all have a "1"

Now If TxtDesc1.value is not equal to "", then Look at TxtDesc2 and so on. Hope that helps. I would appreciate any help.