Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
TAB properties in a UserForm - Requesting Help from Excel VBA Guru
Hello All, I'm having a difficult time with a rather simple bug (or so I hope). Using VBA, I've created a UserForm. Once Data is entered, it is submitted and then inserted into an Excel Worksheet. This is done with the usual code, for example: Cells(x, y).Value = Me.TextBox1.Value And to empty it Me.TextBox1.Value = Empty It works fine, except for the following situation. When I open the worksheet and open the form, the first time I submit information everything works fine. However, after I submit it once, the next time I go to enter more information in the UserForm (without closing it and reopening, just after it has been cleared from the first entry), I can no longer tab from field to field. Instead, it just tabs 5 spaces in the field. Remember, the first time the UserForm is opened it works fine. Any ideas? Again, if I close the UserForm and open it again, it works fine. But if I just submit the information, clear the fields, and go from there, the tab doesn't work. Please help me before I go completely insane!!! Thank you very much!!! DNAHAWKS |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
TAB properties in a UserForm - Requesting Help from Excel VBA Guru
My simple test doesn't show that problem. Post the code.
-- HTH RP (remove nothere from the email address if mailing direct) "DNAHAWKS" wrote in message ... Hello All, I'm having a difficult time with a rather simple bug (or so I hope). Using VBA, I've created a UserForm. Once Data is entered, it is submitted and then inserted into an Excel Worksheet. This is done with the usual code, for example: Cells(x, y).Value = Me.TextBox1.Value And to empty it Me.TextBox1.Value = Empty It works fine, except for the following situation. When I open the worksheet and open the form, the first time I submit information everything works fine. However, after I submit it once, the next time I go to enter more information in the UserForm (without closing it and reopening, just after it has been cleared from the first entry), I can no longer tab from field to field. Instead, it just tabs 5 spaces in the field. Remember, the first time the UserForm is opened it works fine. Any ideas? Again, if I close the UserForm and open it again, it works fine. But if I just submit the information, clear the fields, and go from there, the tab doesn't work. Please help me before I go completely insane!!! Thank you very much!!! DNAHAWKS |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
TAB properties in a UserForm - Requesting Help from Excel VBA Guru
It's long, and I'm a self-taught rookie, so any other suggestins are
welcomed!!! Private Sub CommandButton1_Click() Dim irow As Long Dim ws As Worksheet Dim UBPNumber As Long Dim x As String Dim sFileName As String x = ActiveWorkbook.Path Set ws = Worksheets("Partner List") 'find first empty row If Cells(1, 1).Value = "" Then irow = 1 Else irow = ActiveSheet.UsedRange.Rows.Count + 1 End If 'copy data into spreadsheet If ws.Cells(2, 1).Value = "" Then UBPNumber = 10001 Else UBPNumber = ws.Cells(irow - 1, 1).Value + 1 End If ws.Cells(irow, 1).Value = UBPNumber ws.Cells(irow, 2).Value = Me.txtbusname.Value ws.Cells(irow, 3).Value = Me.txtcontact.Value ws.Cells(irow, 4).Value = Me.textphone.Value ws.Cells(irow, 5).Value = Me.textcity.Value ws.Cells(irow, 6).Value = Me.textemail.Value ws.Cells(irow, 7).Value = Me.comboemp.Value ws.Cells(irow, 8).Value = Me.comborev.Value ws.Cells(irow, 9).Value = Me.Combohear.Value ws.Cells(irow, 10).Value = Me.combojoin.Value ws.Cells(irow, 11).Value = Me.txtnumpkg.Value ws.Cells(irow, 12).Value = Me.combodolpkg.Value ws.Cells(irow, 13).Value = Me.comboshipper.Value ws.Cells(irow, 14).Value = Me.comboos.Value ws.Cells(irow, 15).Value = Me.textoss.Value ws.Cells(irow, 16).Value = Me.combotech.Value ws.Cells(irow, 17).Value = Me.texttech.Value If Me.combojoin.Value = "Yes" Then MsgBilling = "Would you like to add a billing file for this new partner?" AnsBilling = MsgBox(MsgBilling, vbYesNo, "Add Billing File") If AnsBilling = vbYes Then Application.ScreenUpdating = False sFileName = x & "\Billing Templete.xls" 'Need to Change This to Open Billing Templete Set Templete = Workbooks.Open(sFileName) 'Put in Information Worksheets("Billing Summary").Range("E1:E5").Select With Selection.Font .Name = "Trebuchet MS" .Size = 10 End With Range("E2").Select ActiveCell.FormulaR1C1 = Me.txtbusname.Value Range("E3").Select ActiveCell.FormulaR1C1 = Me.txtcontact.Value Range("E4").Select ActiveCell.FormulaR1C1 = Me.textcity.Value Range("E5").Select ActiveCell.FormulaR1C1 = Me.textphone.Value Range("E1").Select ActiveCell.FormulaR1C1 = "Partner #" & UBPNumber Range("E1:E5").Select With Selection .HorizontalAlignment = xlRight End With Range("E1").Select 'Add info in monthly spreadsheet Worksheets("January").Activate Range("A8:I8").Select With Selection .HorizontalAlignment = xlCenter End With Selection.Merge With Selection.Font .Name = "Trebuchet MS" .Size = 18 End With ActiveCell.FormulaR1C1 = Me.txtbusname.Value Range("A9:I9").Select With Selection .HorizontalAlignment = xlCenter End With Selection.Merge With Selection.Font .Name = "Trebuchet MS" .Size = 14 End With ActiveCell.FormulaR1C1 = Me.textcity.Value Range("A10:I10").Select With Selection .HorizontalAlignment = xlCenter End With Selection.Merge With Selection.Font .Name = "Trebuchet MS" .Size = 14 End With ActiveCell.FormulaR1C1 = "Partner #" & UBPNumber 'Copy and Paste to Other Spreadsheets Worksheets("January").Activate Range("A8:I10").Select Selection.Copy Worksheets("March").Paste Destination:=Worksheets("March").Range("A8:A10") Worksheets("April").Paste Destination:=Worksheets("April").Range("A8:A10") Worksheets("May").Paste Destination:=Worksheets("May").Range("A8:A10") Worksheets("June").Paste Destination:=Worksheets("June").Range("A8:A10") Worksheets("July").Paste Destination:=Worksheets("July").Range("A8:A10") Worksheets("August").Paste Destination:=Worksheets("August").Range("A8:A10") Worksheets("September").Paste Destination:=Worksheets("September").Range("A8:A10 ") Worksheets("October").Paste Destination:=Worksheets("October").Range("A8:A10") Worksheets("November").Paste Destination:=Worksheets("November").Range("A8:A10" ) Worksheets("December").Paste Destination:=Worksheets("December").Range("A8:A10" ) Worksheets("February").Paste Destination:=Worksheets("February").Range("A8:A10" ) Worksheets("January").Range("A1").Select ActiveWorkbook.SaveAs Filename:=x & "\" & UBPNumber & ".xls" ActiveWorkbook.Close Application.ScreenUpdating = True Workbooks("UBPBS").Worksheets("Partner List").Activate End If End If 'clear form Me.txtbusname.Value = Empty Me.txtcontact.Value = Empty Me.textphone.Value = Empty Me.textcity.Value = Empty Me.textemail.Value = Empty Me.comboemp.Value = Empty Me.comborev.Value = Empty Me.Combohear.Value = Empty Me.combojoin.Value = Empty Me.txtnumpkg.Value = Empty Me.combodolpkg.Value = Empty Me.comboshipper.Value = Empty Me.comboos.Value = Empty Me.textoss.Value = Empty Me.combotech.Value = Empty Me.texttech.Value = Empty Me.txtbusname.SetFocus End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Properties window show nothing for a userform | Excel Discussion (Misc queries) | |||
how do I turn off requesting scan when opening Excel (Office XP) | Excel Discussion (Misc queries) | |||
Properties transferring from excel cells to word file properties | Excel Programming | |||
Setting properties of userform controls with VBA | Excel Programming | |||
looping through userform controls changing enabled and locked properties | Excel Programming |