PDA

View Full Version : Form List Help



Emoncada
05-04-2007, 02:14 PM
I have this code
'dim the myList variable outside of the subs so it is a public variable.
Dim myList As Variant
'------------------------------
Sub Button3600()
myList = "HP LASERJET 3600N,10FT. USB PRINTER CABLE"
Call Bundle_Main
End Sub
'------------------------------
Sub Button4250()
myList = "HP LASERJET 4250TN,14FT. PATCH CABLE"
Call Bundle_Main
End Sub
'------------------------------
Sub Button4700()
myList = "HP LASERJET 4700DTN,14FT. PATCH CABLE,HP 500 SHEET TRAY"
Call Bundle_Main
End Sub
'------------------------------
Sub Button2015()
myList = "HP LASERJET 2015,10FT. USB PRINTER CABLE"
Call Bundle_Main
End Sub
Sub Bundle_Main()
Dim r As Range, rngData As Range
Dim myNum As Long, i 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:C260")
Set r = rngData.Find("*", SearchDirection:=xlPrevious)
If r Is Nothing Then
Set r = rngData(1)
ElseIf r.Row >= 260 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 >= 256 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

I need this to work with a form I created.
Basically what this does is when a button is pressed it imported the list in it's appropriate location. I need to do the same only difference it's a form not cells it would go into Txtboxes.

Can I do that. Maybe there is a way to simplify this.

mdmackillop
05-06-2007, 11:40 AM
Can you post a workbook to clarify?

Norie
05-06-2007, 11:50 AM
Are you using a userform?

If so what are you trying to do exactly?

You mention a 'list', do you mean a listbox?

Emoncada
05-06-2007, 12:35 PM
Ok sorry for the confusion. I created a userform that I would like to have cmdbuttons that would auto populate txtboxes on the form. So if cmdbutton1 would be clicked it would auto populate a range of txtboxes on the form with items attached to that cmdbutton1. The part that I think would be the hardest is having it look at the range of txtboxes and basically looking for the next available txtbox.

Norie
05-06-2007, 12:41 PM
Do you really need to use textboxes?

Wouldn't a listbox be better especially if you are dealing with varying amounts of data?

Bob Phillips
05-06-2007, 12:43 PM
How about this?

Emoncada
05-06-2007, 12:54 PM
This is the form.
Hope this helps

Emoncada
05-06-2007, 01:03 PM
xld that looks good but how can I have it look to see if data was already entered. And if so then to look at the next row.

Norie
05-06-2007, 01:03 PM
48+ textboxes.:bug:

Emoncada
05-06-2007, 01:14 PM
Is there any way to group txtboxes? So if the first row of the txtboxes has txt then look at the next row. kind of like 12 rows instead of 48+.

Bob Phillips
05-06-2007, 01:37 PM
which tbs would the item go in?

Emoncada
05-06-2007, 01:50 PM
I didn't finish naming them but it should be
TxtDesc1 would be the item, but then depending on that item TxtSn1 would be blank if the item needs a Serial Number or Expense if it's an expense item, or phone if item is a phone. So each item would need to have something attached.
Hope that explains. Thanks XLD for your help

Bob Phillips
05-06-2007, 01:53 PM
What I don't get is that in your original, each button had 3 items associated. Where do they go in relation to those columns of Textboxes si what I am really asking.

Emoncada
05-06-2007, 02:11 PM
ok sorry.
example
If cmdbutton1 was clicked then
TxtDesc1 = "NC8230"...............TxtSn1 = ""...............TxtQua1 = 1
TxtDesc2 = "Laptop Bag"...........TxtSn2 = "Expense".....TxtQua2 = 1
TxtDesc3 = "Battery"................TxtSn3 = "Expense"......TxtQua3 = 1

If cmdbutton2 was clicked then
TxtDesc4 = "Dc7700".................TxtSn4 = ""...............TxtQua4 = 1
TxtDesc5 = "Power Cord"............TxtSn5 = "Expense".....TxtQua5 = 1
TxtDesc6 = "Keyboard"................TxtSn6 = "Expense"......TxtQua6 = 1

If you notice cmdbutton2 would go under cmdbutton1 not overright it. I need it to look for next available row or next available TxtDesc Box.

Hope that helps

Bob Phillips
05-06-2007, 02:23 PM
.

Emoncada
05-06-2007, 02:40 PM
That looks good xld Great. Now can I add something to that.
I wanted to get this part first before adding the second part.
When the cmdbutton is clicked Can I have it look if it's already there if so then combine any item that are the same except if the item needs a Serial Number? Oh and Can there be a MsgBox "No More Room" when you can't add no more items?

Emoncada
05-07-2007, 10:41 AM
Possibly a popup that would ask how many? then just Place them by that amount.