Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
HELP WITH A FORM IN EXCEL.
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/a0e6...erate_List_xls I have modified the form which is in file "Generate List required" at http://www.filefactory.com/file/a0e6...t_required_xls This is the file that needs to be fixed. Thankyou. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with a form in excel.
I've not looked at your workbook (i'm not keen on downloading from online file stores) however here's a couple of hings you could work with, the code goes in the form code module: Code: -------------------- 'runs as the form is opened Private Sub UserForm_initialize() 'declare our variables Dim Rng As Range Dim MyCell As Range 'set a range to work with Set Rng = Sheets("Sheet1").Range("A1:A" & Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row) 'loop through each cell in the range For Each MyCell In Rng 'add the value of the cell to the combobx Me.ComboBox1.AddItem (MyCell) 'next cell Next MyCell End Sub Private Sub ComboBox2_Change() 'sets the format of the combobox Me.ComboBox2.Value = Format(Me.ComboBox2, "dd/mm/yyyy") End Sub -------------------- Sinner;521762 Wrote: 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--------------------- Code: -------------------- 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 'Generate_List.xls - download now for free. File sharing. Software file sharing. Free file hosting. File upload. FileFactory.com' (http://www.filefactory.com/file/a0e6...erate_List_xls) I have modified the form which is in file "Generate List required" at '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/a0e6...t_required_xls) This is the file that needs to be fixed. Thankyou. -- Simon Lloyd Regards, Simon Lloyd 'Microsoft Office Help' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=143196 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with a form in excel.
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with a form in excel.
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/a0g0...t_required_xls Thank you |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with a form in excel.
Angela, you can add a date picker (RoyUk has added one here http://www.thecodecage.com/forumz/do...ndar-form.html you may have to join for free to download it), i again haven't looked at your workbook, you can join our forums (shown in the link below) it's completely free, if you do join you will have the opportunity to add attachments to your posts so you can add workbooks to better illustrate your problems and get help directly with them. Also if you do join please post in this thread (link found below) so that people who have 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 problems :) Angela;522470 Wrote: 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------------------------------------------------ Code: -------------------- 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: '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/a0g0...t_required_xls) Thank you (shown in the link below) it's completely free, if you do join you will have the opportunity to add attachments to your posts so you can add workbooks to better illustrate your problems and get help directly with them. Also if you do join please post in this thread (link found below) so that people who have been following or helping with this query can continue to do so. :) -- Simon Lloyd Regards, Simon Lloyd 'Microsoft Office Help' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=143196 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with a form in excel.
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/a0g0...t_required_xls Thank you -- mistake ------------------------------------------------------------------------ mistake's Profile: http://www.thecodecage.com/forumz/me...hp?userid=1031 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=143196 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with a form in excel.
The file is attached now.. +-------------------------------------------------------------------+ |Filename: Generate_List_required.xls | |Download: http://www.thecodecage.com/forumz/attachment.php?attachmentid=321| +-------------------------------------------------------------------+ -- mistake ------------------------------------------------------------------------ mistake's Profile: http://www.thecodecage.com/forumz/me...hp?userid=1031 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=143196 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with a form in excel.
On Oct 13, 7:41*pm, mistake wrote:
The file is attached now.. +-------------------------------------------------------------------+ |Filename: Generate_List_required.xls * * * * * * * * * * * * * * * | |Download:http://www.thecodecage.com/forumz/attachment.php?attachmentid=321| +-------------------------------------------------------------------+ -- mistake ------------------------------------------------------------------------ mistake's Profile:http://www.thecodecage.com/forumz/me...hp?userid=1031 View this thread:http://www.thecodecage.com/forumz/sh...d.php?t=143196 Can someone help me with this?? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
excel form. Need data extracted to spreadsheet each time a form co | Excel Discussion (Misc queries) | |||
Can a form made in Excel 2002 be converted into a fillable form? | Excel Discussion (Misc queries) | |||
Transfer data to form from Excel range upon loading of form. | Excel Programming | |||
I created a form on excel. want to edit the form without printing | Excel Discussion (Misc queries) | |||
form in excel to be attached to the emails address in the form upon sumission | Excel Programming |