Okay, so I am trying to set this User Form to autofill the next blank line. It should, and does, fill the information into columns A:D. However, I have formulas in columns E:L and when I fill those formula's down (so that they will autofill as new information is added through the form) the macro forces the information to the bottom of the rows (the first row with no formula's filled down in E:L)
How can I change this to have it fill based on the blanks only being found in A:D (and ignore the formula's in E:L)
Someone fix this to insert the info in the next blank line between A:D (formulas are in E and on, so its skipping to the bottom of where the formulas are)
Problem Coding:
' Write data to worksheet
RowCount = Worksheets("Custom Built Button").Range("A3").CurrentRegion.Rows.Count
With Worksheets("Custom Built Button").Range("A3")
.Offset(RowCount, 0).Value = Me.IC_Name.Value
.Offset(RowCount, 1).Value = Me.cmboOpportunity.Value
.Offset(RowCount, 2).Value = Me.CostCenter.Value
.Offset(RowCount, 3).Value = Me.ExpenseCat.Value
End With
Entire Code:
Private Sub UserForm_Initialize()
Dim c As Range
With Sheets("SFICInfo")
For Each c In .Range("A2", .Range("A" & Rows.Count).End(xlUp))
If c.Value <> "" Then IC_Name.AddItem c.Value
Next c
End With
With Sheets("SFWorkOrder")
For Each c In .Range("B2", .Range("B" & Rows.Count).End(xlUp))
If c.Value <> "" Then cmboOpportunity.AddItem c.Value
Next c
End With
End Sub
Private Sub cmdOK_Click()
Dim RowCount As Long
Dim ctl As Control
If Me.IC_Name.Value = "" Then
MsgBox "Please enter IC Name.", vbExclamation, "Information Missing"
Me.IC_Name.SetFocus
Exit Sub
End If
If Me.cmboOpportunity.Value = "" Then
MsgBox "Please choose an Opportunity.", vbExclamation, "Information Missing"
Me.cmboOpportunity.SetFocus
Exit Sub
End If
If Me.CostCenter.Value = "" Then
MsgBox "Please enter a Cost Center.", vbExclamation, "Information Missing"
Me.CostCenter.SetFocus
Exit Sub
End If
If Me.ExpenseCat.Value = "" Then
MsgBox "Please enter an Expense Catagory.", vbExclamation, "Information Missing"
Me.ExpenseCat.SetFocus
Exit Sub
End If
' Write data to worksheet
RowCount = Worksheets("Custom Built Button").Range("A3").CurrentRegion.Rows.Count
With Worksheets("Custom Built Button").Range("A3")
.Offset(RowCount, 0).Value = Me.IC_Name.Value
.Offset(RowCount, 1).Value = Me.cmboOpportunity.Value
.Offset(RowCount, 2).Value = Me.CostCenter.Value
.Offset(RowCount, 3).Value = Me.ExpenseCat.Value
End With
' Clear the from
For Each ctl In Me.Controls
If TypeName(ctl) = "TextBox" Or TypeName(ctl) = "ComboBox" Then
ctl.Value = ""
End If
Next ctl
End Sub
edit: I would post the file, but due to security reasons, I am not really supposed to.
Bookmarks