HELP WITH A FORM IN EXCEL.

S

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.
 
S

Simon Lloyd

I've not looked at your workbook (i'm not keen on downloading fro
online file stores) however here's a couple of hings you could wor
with, the code goes in the form code module
Code
-------------------

'runs as the form is opene
Private Sub UserForm_initialize(
'declare our variable
Dim Rng As Rang
Dim MyCell As Rang
'set a range to work wit
Set Rng = Sheets("Sheet1").Range("A1:A" & Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
'loop through each cell in the rang
For Each MyCell In Rn
'add the value of the cell to the combob
Me.ComboBox1.AddItem (MyCell
'next cel
Next MyCel
End Su


Private Sub ComboBox2_Change(
'sets the format of the combobo
Me.ComboBox2.Value = Format(Me.ComboBox2, "dd/mm/yyyy"
End Su
-------------------

Sinner;521762 said:
Hi everyone.
Well guys I have this one script below which I got from google bac
sometime
It uses a form to make the lists but generates the list column t
column, like if the list is generated in columnA, the second lis
wil
be in columnB or the next empty column
-----------------script start-------------------- Code
-------------------
Private Sub CommandButton1_Click(
Dim X As Lon
Dim LastColumn As Lon
Dim Number1 As Varian
Dim Number2 As Varian
Dim TBox1 As Strin
Dim TBox2 As Strin
TBox1 = Trim(TextBox1.Text
TBox2 = Trim(TextBox2.Text
If TBox1 = "" Or TBox2 = "" The
MsgBox "You must fill in both text boxes!
ElseIf TBox1 Like String(Len(TBox1), "#") And Len(TBox2) < 29 The
Number1 = CDec(TBox1
If TBox2 Like String(Len(TBox2), "#") And Len(TBox2) < 29 The
Number2 = CDec(TBox2
If Number2 < Number1 The
MsgBox "Ending number must contain an equal or larger numbe
than Starting!
Els
LastColumn = Cells(1, Columns.Count).End(xlToLeft).Colum
If LastColumn = 1 And Range("A1").Value = "" Then LastColum


For X = 0 To Number2 - Number
Cells(X + 1, LastColumn + 1).Value =
"'" & Format$(Number1 + X, String(Len(Trim(TBox1))
"0")
Nex
End I
Els
MsgBox "Bad entry in Ending text box
End I
Els
MsgBox "Bad entry in Starting text box
End I
End Su
-------------------
-----------------script end--------------------


The form at the moment has two text boxe
STAR
EN
with one button "GENERATE LIST". that all


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


Star
En
Item name (to be selected from drop down list which can be update
from time to time with new item names
Location ( to be selected from drop down list which can be update
from time to time with new item names
Date (dd/mm/yyyy
Extra infomation1 text box (additional column which I can use late
o
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 late
on so that i dont bug ya to add another test box in the form fo
me :)
Extra infomation3 text box (additional column which I can use late
on so that i dont bug ya to add another test box in the form fo
me :)


The original file that i'm using is a
'Generate_List.xls - download now for free. File sharing. Softwar
file sharing. Free file hosting. File upload. FileFactory.com
(http://www.filefactory.com/file/a0e6292/n/Generate_List_xls


I have modified the form which is in file "Generate List required" a
'Generate_List_required.xls - download now for free. File sharing
Software file sharing. Free file hosting. File upload
FileFactory.com
(http://www.filefactory.com/file/a0e635h/n/Generate_List_required_xls
This is the file that needs to be fixed

Thankyou
Click to expand...

--
Simon Lloy

Regards
Simon Lloy
'Microsoft Office Help' (http://www.thecodecage.com
 
A

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
 
S

Simon Lloyd

Angela, you can add a date picker (RoyUk has added one her
http://www.thecodecage.com/forumz/downloads/26948-calendar-form.html yo
may have to join for free to download it), i again haven't looked a
your workbook, you can join our forums (shown in the link below) it'
completely free, if you do join you will have the opportunity to ad
attachments to your posts so you can add workbooks to better illustrat
your problems and get help directly with them. Also if you do joi
please post in this thread (link found below) so that people who hav
been following or helping with this query can continue to do so. :

Other than that and some minore issues in your code i see no problem
:

Angela;522470 said:
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
calendar option as to pick date from a calendar
I hope you download the file once & have a look at it to get a bette
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 shee
for combo box data

'--------------------
SCRIPTSTART----------------------------------------------- Code
-------------------
Private Sub UserForm_initialize(
'declare our variable
Dim Rng As Rang
Dim MyCell As Rang
Dim RngLoc As Rang
Dim MyLoc As Rang

'set a range to work wit
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").Rang
("b" & Rows.Count).End(xlUp).Row

'For Ite
For Each MyCell In Rn
Me.ComboBox1.AddItem (MyCell
Next MyCel

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

For X = 0 To Number2 - Number
Cells(X + 1, LastColumn + 1).Value =
"'" & Format$(Number1 + X, String(Len(Trim(TBox1))
"0")
Nex
End I
Els
MsgBox "Bad entry in Ending text box
End I
Els
MsgBox "Bad entry in Starting text box
End I
End Su
-------------------
SCRIPTEND-------------------------------------

Link to the new file is as follow
'Generate_List_required.xls - download now for free. File sharing
Software file sharing. Free file hosting. File upload
FileFactory.com
(http://www.filefactory.com/file/a0g061h/n/Generate_List_required_xls

Thank yo
Click to expand...

(shown in the link below) it's completely free, if you do join yo
will have the opportunity to add attachments to your posts so you ca
add workbooks to better illustrate your problems and get help directl
with them. Also if you do join please post in this thread (link foun
below) so that people who have been following or helping with this quer
can continue to do so. :

--
Simon Lloy

Regards
Simon Lloy
'Microsoft Office Help' (http://www.thecodecage.com
 
M

mistake

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

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 shee

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 yo
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Help needed! Extracting Heading 3 and Contents from a Word Doc to Excel 0
Word macro that can call cells out of Excel for a find and replace 0
Extract Text in braquet and list of terms from 2 Docs 3
Closing PDF file from Excel VBA (Adobe Reader, Adobe Acrobat, Browser) 0
Transpose data into a single cloumn 6
Excel does not accept text format! 0
Help with inline shapes 1
Making a macro only work on certain files 2

Top