TAB properties in a UserForm - Requesting Help from Excel VBA Guru

[Guest]

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

 

[Bob Phillips]

My simple test doesn't show that problem. Post the code.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

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