 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Programming / January 2007entering new data thru combobox
I have a combobox on a userform that is linked to a named range t provide a list for users to select. The form works well in feedin data from the form to a row in the worksheet. My problem is that can't get the userform to feed data to the worksheet when the use inputs new data into the combobox. If new data is added to th combobox I would like this data to be fed to the next empty row belo the row already populated on the worksheet -- buckcho Posted from - http://www.officehelp.i Maybe you could use some of the techniques that Debra Dalgleish used: http://contextures.com/xlUserForm01.html She used textboxes, but the way she wrote to the worksheet should be pretty much the same. > I have a combobox on a userform that is linked to a named range to > provide a list for users to select. The form works well in feeding [quoted text clipped - 11 lines] > > Posted from - http://www.officehelp.in  SignatureDave Peterson I previously reviewed the website but without the ability to add ne data to the textbox there isn't a clear connection to the process wit a combobox. if I use one of the items already listed in the combobo everything works fine and all the data from the userform is copied t the worksheet correctly. It's just when I try adding a new item to th combobox that nothing gets copied over to the worksheet -- buckcho Posted from - http://www.officehelp.i Debra has this in her code: 'find first empty row in database iRow = ws.Cells(Rows.Count, 1) _ .End(xlUp).Offset(1, 0).Row This'll work to find the next available row. And she has these lines in the code: 'check for a part number If Trim(Me.txtPart.Value) = "" Then Me.txtPart.SetFocus MsgBox "Please enter a part number" Exit Sub End If 'copy the data to the database ws.Cells(iRow, 1).Value = Me.txtPart.Value You could change it to: 'check for a part number If Trim(Me.Combobox1.Value) = "" Then Me.txtPart.SetFocus MsgBox "Please enter a part number" Exit Sub End If 'copy the data to the database ws.Cells(iRow, 1).Value = Me.combobox1.Value ====== If this doesn't help, you may want to post more of your code. > I previously reviewed the website but without the ability to add new > data to the textbox there isn't a clear connection to the process with [quoted text clipped - 10 lines] > > Posted from - http://www.officehelp.in SignatureDave Peterson I have attached my code to illustrate my problem. The current cod allows a user to click on the userform combobox (txtBatch1), select a item, enter data in the other textboxes on the userform, and transfe the data to the corresponding row on the worksheet that matched th combobox selection. I also want the user to be able to enter a ne item into the combobox, enter data in the other textboxes, and transfe all this information to the next available row in the worksheet. Private Sub CommandButton1_Click() Dim strRange As String If txtBatch1 = vbNullString Then MsgBox "No batch number", vbCritical txtBatch1.SetFocus Exit Sub End If strRange = txtBatch1.RowSource If txtBatch1.ListIndex > -1 Then With Range(strRange).Cells(txtBatch1.ListIndex + 1, 1) .Offset(0, 1) = IIf(txtDate1 <> vbNullString, txtDate1, .Offset(0 1)) .Offset(0, 2) = IIf(txtCust1 <> vbNullString, txtCust1, .Offset(0 2)) .Offset(0, 3) = IIf(txtBoard1 <> vbNullString, txtBoard1 .Offset(0, 3)) .Offset(0, 4) = IIf(txtSerial1 <> vbNullString, txtSerial1 .Offset(0, 4)) .Offset(0, 5) = IIf(txtQty1 <> vbNullString, txtQty1, .Offset(0 5)) .Offset(0, 16) = IIf(txtStatus1 <> vbNullString, txtStatus1 .Offset(0, 16)) .Offset(0, 17) = IIf(txtNotes <> vbNullString, txtNotes, .Offset(0 17)) End With Else With Range(strRange).Cells(Range(strRange).Rows.Count + 1, 1) .Value = txtBatch1 End With End If txtBatch1 = vbNullString 'clear the data Me.txtBatch1.Value = "" Me.txtDate1.Value = "" Me.txtCust1.Value = "" Me.txtBoard1.Value = "" Me.txtSerial1.Value = "" Me.txtQty1.Value = "" Me.txtStatus1.Value = "" Me.txtNotes.Value = "" Me.txtBatch1.SetFocus End Su -- buckcho Posted from - http://www.officehelp.i So the .rowsource refers to a dynamic range name??? If it doesn't, then you may want it to. Check how Debra Dalgleish does it: http://contextures.com/xlNames01.html#Dynamic This is completely untested. I didn't take the time to set up a test environment. Option Explicit Private Sub CommandButton1_Click() Dim strRange As String Dim DestCell As Range If txtbatch1 = vbNullString Then MsgBox "No batch number", vbCritical txtbatch1.SetFocus Exit Sub End If strRange = txtbatch1.RowSource If txtbatch1.ListIndex > -1 Then Set DestCell = Range(strRange).Cells(txtbatch1.ListIndex + 1, 1) Else Set DestCell = Range(strRange).Cells(Range(strRange).Rows.Count + 1, 1) DestCell.Value = txtbatch1 End If With DestCell .Offset(0, 1) = IIf(txtDate1 <> vbNullString, txtDate1, .Offset(0, 1)) .Offset(0, 2) = IIf(txtCust1 <> vbNullString, txtCust1, .Offset(0, 2)) .Offset(0, 3) = IIf(txtBoard1 <> vbNullString, _ txtBoard1, .Offset(0, 3)) .Offset(0, 4) = IIf(txtSerial1 <> vbNullString, _ txtSerial1, .Offset(0, 4)) .Offset(0, 5) = IIf(txtQty1 <> vbNullString, txtQty1, .Offset(0, 5)) .Offset(0, 16) = IIf(txtStatus1 <> vbNullString, _ txtStatus1, .Offset(0, 16)) .Offset(0, 17) = IIf(txtNotes <> vbNullString, _ txtNotes, .Offset(0, 17)) End With 'clear the data Me.txtbatch1.Value = "" Me.txtDate1.Value = "" Me.txtCust1.Value = "" Me.txtBoard1.Value = "" Me.txtSerial1.Value = "" Me.txtQty1.Value = "" Me.txtStatus1.Value = "" Me.txtNotes.Value = "" Me.txtbatch1.SetFocus End Sub > I have attached my code to illustrate my problem. The current code > allows a user to click on the userform combobox (txtBatch1), select an [quoted text clipped - 68 lines] > > Posted from - http://www.officehelp.in SignatureDave Peterson It works!!!!!! The only problem is that it enters the new data at ro 65000 in the worksheet instead of the next empty row from the top -- buckcho Posted from - http://www.officehelp.i What did you define for the rowsource? Did you you a dynamic name? > It works!!!!!! The only problem is that it enters the new data at row > 65000 in the worksheet instead of the next empty row from the top. [quoted text clipped - 6 lines] > > Posted from - http://www.officehelp.in SignatureDave Peterson The rowsource is defined with a named range for the combobox -- buckcho Posted from - http://www.officehelp.i What does it refer to? Did you make it dynamic so that it grows and contracts with your data? > The rowsource is defined with a named range for the combobox. > [quoted text clipped - 5 lines] > > Posted from - http://www.officehelp.in SignatureDave Peterson Yes, it's a dynamic named range. It appears that I need to add a lin to have the new data entered in the next available row and to set up nullstring to prevent overwriting -- buckcho Posted from - http://www.officehelp.i I would have thought that this line: Set DestCell = Range(strRange).Cells(Range(strRange).Rows.Count + 1, 1) put the next entry directly under the last used entry in that strRange. Maybe you could add some: Debug.print range(strrange).address and debug.print destcell.address to see what is really being used. > Yes, it's a dynamic named range. It appears that I need to add a line > to have the new data entered in the next available row and to set up a [quoted text clipped - 7 lines] > > Posted from - http://www.officehelp.in SignatureDave Peterson My mistake Dave. I thought I had set it up as a dynamic range but i fact it was just a named range. After changing it to a dynamic rang things seem to be more the norm. The weird thing though is the firs time a new number is added to the combobox it puts the data one ro down from the next blank row on the spreadsheet. Additional ne entries after the initial entry are added correctly in the next empt row -- buckcho Posted from - http://www.officehelp.i After you create the name, use Edit|Goto and type that name. My bet is that your range is too large. > My mistake Dave. I thought I had set it up as a dynamic range but in > fact it was just a named range. After changing it to a dynamic range [quoted text clipped - 11 lines] > > Posted from - http://www.officehelp.in SignatureDave Peterson Also, if I enter another new item in the combox the data overrides th previous new item saved to the worksheet -- buckcho Posted from - http://www.officehelp.i The dynamic range shown below is the one I'm using but it doesn't sho up in the Edit / GoTo box although it does show up in the Insert / Nam / Define box. =OFFSET(Data!$A$2,0,0,COUNTA(Data!$A:$A),1 -- buckcho Posted from - http://www.officehelp.i If there's something in A1 (a header is common), then the count is one more than you want: So you can just subtract that header row: =OFFSET(Data!$A$2,0,0,COUNTA(Data!$A:$A)-1,1) Or you can do something not as pretty: =OFFSET(Data!$A$2,0,0,COUNTA(Data!$A2:$A65536),1) (I like the top and make sure that there's a header.) And that's the way dynamic ranges work--they don't show up. But you can still type it in the Edit|Goto dialog. > The dynamic range shown below is the one I'm using but it doesn't show > up in the Edit / GoTo box although it does show up in the Insert / Name [quoted text clipped - 9 lines] > > Posted from - http://www.officehelp.in SignatureDave Peterson Thanks for all your help Dave! Your suggestion worked perfectly and no my form is working the way I envisioned. As complicated as this projec was, with 3 different forms entering data into the same worksheet, yo were the only one who hung in there. Thanks again, Do -- buckcho Posted from - http://www.officehelp.i I think you did, too! Glad you got it working. > Thanks for all your help Dave! Your suggestion worked perfectly and now > my form is working the way I envisioned. As complicated as this project [quoted text clipped - 11 lines] > > Posted from - http://www.officehelp.in SignatureDave Peterson |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.