Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Form trouble
Hi,
With help on this board, I've been able to create a form with comboboxes and listboxes that enters data in the next empty row of a sheet named "METRO". partial code below: Private Sub ADD_Click() Dim r As Long Dim ws As Worksheet Set ws = Worksheets("METRO") r = ws.Cells(Rows.Count, 5).End(xlUp).Offset(1, 0).Row ws.Range("C" & r).Value = ComboBox1.Value ws.Range("E" & r).Value = rngsw.Value ws.Range("F" & r).Value = rngfail.Value ws.Range("H" & r).Value = ComboBox2.Value ws.Range("I" & r).Value = ComboBox3.Value ws.Range("J" & r).Value = ComboBox4.Value ws.Range("K" & r).Value = ComboBox5.Value ws.Range("L" & r).Value = ComboBox6.Value ws.Range("T" & r).Value = ComboBox7.Value ws.Range("AA" & r).Value = ComboBox8.Value ws.Range("AD" & r).Value = Timestart.Value ws.Range("AE" & r).Value = Timeend.Value ws.Range("M" & r).Value = ComboBox9.Value ComboBox1.Value = "" rngsw.Value = "" rngfail.Value = "" ComboBox7.Value = "" ComboBox8.Value = "" Timestart.Value = "" Timeend.Value = "" ComboBox9.Value = "" ComboBox2.SetFocus Unload CIDATA CIDATA.Show End Sub If possible, I would like to create a similar form which lists rows that contain a "Y" in column E. Any Ideas? Thanks --- Message posted from http://www.ExcelForum.com/ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Form trouble
It appears that the CIDATA userform is cleared after each addition to the
form. So you need to specify how the new userform is controlled. To control what rows are listed in the combo boxes using the value in a cell (column E = Y in your OP) you will need to fill the boxes under programme control. How are the current comboBox list values currently set up? Suggest you post the rest of the userform code to help understand what is happening. Cheers Nigel "Oreg " wrote in message ... Hi, With help on this board, I've been able to create a form with comboboxes and listboxes that enters data in the next empty row of a sheet named "METRO". partial code below: Private Sub ADD_Click() Dim r As Long Dim ws As Worksheet Set ws = Worksheets("METRO") r = ws.Cells(Rows.Count, 5).End(xlUp).Offset(1, 0).Row ws.Range("C" & r).Value = ComboBox1.Value ws.Range("E" & r).Value = rngsw.Value ws.Range("F" & r).Value = rngfail.Value ws.Range("H" & r).Value = ComboBox2.Value ws.Range("I" & r).Value = ComboBox3.Value ws.Range("J" & r).Value = ComboBox4.Value ws.Range("K" & r).Value = ComboBox5.Value ws.Range("L" & r).Value = ComboBox6.Value ws.Range("T" & r).Value = ComboBox7.Value ws.Range("AA" & r).Value = ComboBox8.Value ws.Range("AD" & r).Value = Timestart.Value ws.Range("AE" & r).Value = Timeend.Value ws.Range("M" & r).Value = ComboBox9.Value ComboBox1.Value = "" rngsw.Value = "" rngfail.Value = "" ComboBox7.Value = "" ComboBox8.Value = "" Timestart.Value = "" Timeend.Value = "" ComboBox9.Value = "" ComboBox2.SetFocus Unload CIDATA CIDATA.Show End Sub If possible, I would like to create a similar form which lists rows that contain a "Y" in column E. Any Ideas? Thanks --- Message posted from http://www.ExcelForum.com/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Form trouble
Hey Nigel,
All of my Comboboxes are populated with selections I've created. Cod below: Hope this is what your looking for. Private Sub UserForm_Initialize() ComboBox1.AddItem "Out-of-Process" ComboBox1.AddItem "In-Process" ComboBox1.AddItem "Environmental" ComboBox1.AddItem "TBD" ComboBox1.AddItem "IRU" ComboBox2.AddItem "A" ComboBox2.AddItem "B" ComboBox2.AddItem "N/A" ComboBox2.AddItem "?" ComboBox3.AddItem "PCI" ComboBox3.AddItem "CI" ComboBox3.AddItem "N/A" ComboBox3.AddItem "?" ComboBox4.AddItem "Y" ComboBox4.AddItem "N" ComboBox4.AddItem "N/A" ComboBox4.AddItem "?" ComboBox5.AddItem "Y" ComboBox5.AddItem "N" ComboBox5.AddItem "N/A" ComboBox5.AddItem "?" ComboBox6.AddItem "Y" ComboBox6.AddItem "N" ComboBox6.AddItem "N/A" ComboBox6.AddItem "?" ComboBox7.AddItem "JK" ComboBox7.AddItem "Perm" ComboBox7.AddItem "Other" ComboBox7.AddItem "Reel" ComboBox7.AddItem "Slack" ComboBox8.AddItem "Y" ComboBox8.AddItem "N" ComboBox9.AddItem "T" ComboBox9.AddItem "P" ComboBox9.AddItem "N/A" ComboBox9.AddItem "?" Dim r As Long Dim ws As Worksheet Set ws = Worksheets("METRO") With ws If .AutoFilterMode Then If .FilterMode Then .ShowAllData End If End If End With r = ws.Cells(Rows.Count, 8).End(xlUp).Offset(1, 0).Row TextBox1.Text = ws.Range("D" & r).Value TextBox2.Text = ws.Range("B" & r).Value TextBox3.Text = ws.Range("G" & r).Value TextBox4.Text = ws.Range("AH" & r).Value End Su -- Message posted from http://www.ExcelForum.com |
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) | |||
Using a template form, advance a form number everytime you open | Excel Discussion (Misc queries) | |||
Trouble with saving user input in an Excel Form. | Excel Discussion (Misc queries) | |||
Textbox trouble on a user form | Excel Programming | |||
User form trouble | Excel Programming |