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/ |
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/ |
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 |
All times are GMT +1. The time now is 12:35 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com