HELP WITH A FORM IN EXCEL.

[Sinner]

Hi everyone..
Well guys I have this one script below which I got from google back
sometime.
It uses a form to make the lists but generates the list column to
column, like if the list is generated in columnA, the second list
will
be in columnB or the next empty column.
-----------------script start---------------------
Private Sub CommandButton1_Click()
Dim X As Long
Dim LastColumn As Long
Dim Number1 As Variant
Dim Number2 As Variant
Dim TBox1 As String
Dim TBox2 As String
TBox1 = Trim(TextBox1.Text)
TBox2 = Trim(TextBox2.Text)
If TBox1 = "" Or TBox2 = "" Then
MsgBox "You must fill in both text boxes!"
ElseIf TBox1 Like String(Len(TBox1), "#") And Len(TBox2) < 29 Then
Number1 = CDec(TBox1)
If TBox2 Like String(Len(TBox2), "#") And Len(TBox2) < 29 Then
Number2 = CDec(TBox2)
If Number2 < Number1 Then
MsgBox "Ending number must contain an equal or larger number
than Starting!"
Else
LastColumn = Cells(1, Columns.Count).End(xlToLeft).Column
If LastColumn = 1 And Range("A1").Value = "" Then LastColumn
=
0
For X = 0 To Number2 - Number1
Cells(X + 1, LastColumn + 1).Value = _
"'" & Format$(Number1 + X, String(Len(Trim(TBox1)),
"0"))
Next
End If
Else
MsgBox "Bad entry in Ending text box"
End If
Else
MsgBox "Bad entry in Starting text box"
End If
End Sub
-----------------script end---------------------


The form at the moment has two text boxes
START
END
with one button "GENERATE LIST". that all.


I was wondering if you can add another drop downlist in the form with
item name list and a text box with date in it.
User input will require


Start
End
Item name (to be selected from drop down list which can be updated
from time to time with new item names)
Location ( to be selected from drop down list which can be updated
from time to time with new item names )
Date (dd/mm/yyyy)
Extra infomation1 text box (additional column which I can use later
on
so that i dont bug ya to add another test box in the form for me )
Extra infomation2 text box (additional column which I can use later
on so that i dont bug ya to add another test box in the form for
me )
Extra infomation3 text box (additional column which I can use later
on so that i dont bug ya to add another test box in the form for
me )


The original file that i'm using is at
http://www.filefactory.com/file/a0e6292/n/Generate_List_xls


I have modified the form which is in file "Generate List required" at
http://www.filefactory.com/file/a0e635h/n/Generate_List_required_xls
This is the file that needs to be fixed.

Thankyou.

 

[Angela]

Link to the new file is as follow:
http://www.filefactory.com/file/a0g061h/n/Generate_List_required_xls

Thanks

 

[Angela]

Hello Simon,

I have made some changes, but I'm unable to get the desired results.
The date needs to be in a text box. I was thinking may be we can add a
calendar option as to pick date from a calendar.
I hope you download the file once & have a look at it to get a better
idea.... I'm sure you'll get to know the requirement.

I have added a sheet1 with the ranges which will serve as a temp sheet
for combo box data.

'---------------------
SCRIPTSTART------------------------------------------------
Private Sub UserForm_initialize()
'declare our variables
Dim Rng As Range
Dim MyCell As Range
Dim RngLoc As Range
Dim MyLoc As Range

'set a range to work with
Set Rng = Sheets("Sheet1").Range("A2:A" & Sheets("Sheet1").Range("A"
& Rows.Count).End(xlUp).Row)
Set RngLoc = Sheets("Sheet1").Range("b2:b" & Sheets("Sheet1").Range
("b" & Rows.Count).End(xlUp).Row)

'For Item
For Each MyCell In Rng
Me.ComboBox1.AddItem (MyCell)
Next MyCell

'For Location
For Each MyLoc In RngLoc
Me.ComboBox2.AddItem (MyLoc)
Next MyLoc
Me.TextBox3.Text = Format(Me.TextBox3.Text, "dd/mm/yyyy")
End Sub
Private Sub CommandButton1_Click()
Dim X As Long
Dim LastColumn As Long
Dim Number1 As Variant
Dim Number2 As Variant
Dim TBox1 As String
Dim TBox2 As String
TBox1 = Trim(TextBox1.Text)
TBox2 = Trim(TextBox2.Text)
If TBox1 = "" Or TBox2 = "" Then
MsgBox "You must fill in both text boxes!"
ElseIf TBox1 Like String(Len(TBox1), "#") And Len(TBox2) < 29 Then
Number1 = CDec(TBox1)
If TBox2 Like String(Len(TBox2), "#") And Len(TBox2) < 29 Then
Number2 = CDec(TBox2)
If Number2 < Number1 Then
MsgBox "Ending number must contain an equal or larger number
than Starting!"
Else
LastColumn = Cells(1, Columns.Count).End(xlToLeft).Column
If LastColumn = 1 And Range("A3").Value = "" Then LastColumn =

For X = 0 To Number2 - Number1
Cells(X + 1, LastColumn + 1).Value = _
"'" & Format$(Number1 + X, String(Len(Trim(TBox1)),
"0"))
Next
End If
Else
MsgBox "Bad entry in Ending text box"
End If
Else
MsgBox "Bad entry in Starting text box"
End If
End Sub
'--------------------------------
SCRIPTEND--------------------------------------

Link to the new file is as follow:
http://www.filefactory.com/file/a0g061h/n/Generate_List_required_xls

Thank you

 

[Angela]

The file is attached now..

+-------------------------------------------------------------------+
|Filename: Generate_List_required.xls                              |
|Download:http://www.thecodecage.com/forumz/attachment.php?attachmentid=321|
+-------------------------------------------------------------------+

Can someone help me with this??