Hi after days and days of research i have hit a stumbling block with my code and i hope you can spare a minute to help me.
i have a workbook with two worksheets, Documents and Workings. The user can fill in the userform i have created so that it fills in the worksheet in documents accordingly.
My code is as folows:
What i am trying to achieve is auto numbering in column B in Documents with D1, D2, etc.
Each time the userform is opened or restarted or populated by a the command button it populates the D1, D2 etc textbox on the userform and then the form submits the alll the data to the next free row in the worksheet.
As you will see i have with my very limited knowledge created a work round utilising the workings worksheet to populate column L and then transfers this to the userform. This code relys on the cell above A2 to be a number.
Is it possible to dispense with my workround and get the userform to populate the textbox automatically as it reads from column B in the documents worksheet, collects the last D number used and allocates the next one, if the form is blank then it should allocate D1 in the first instance.
I do hope i have made some sense and that this last hurdle can be overcome, thank you in anticipation,
regards
Lee
i have a workbook with two worksheets, Documents and Workings. The user can fill in the userform i have created so that it fills in the worksheet in documents accordingly.
My code is as folows:
Code:
Private Sub cmdAdd_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Documents")
'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row
'check for a Doc Number
If Trim(Me.TextBoxDocNo.Value) = "" Then
Me.TextBoxDocNo.SetFocus
MsgBox "Please Create a Document Number by the using the Create Button"
Exit Sub
End If 'check for a Date
If Trim(Me.txtCal.Value) = "" Then
Me.txtCal.SetFocus
MsgBox "Please enter a Date"
Exit Sub
End If
'check for a Description
If Trim(Me.txtDescription.Value) = "" Then
Me.txtDescription.SetFocus
MsgBox "Please enter a Description"
Exit Sub
End If
'check for a Location
If Trim(Me.txtLocation.Value) = "" Then
Me.txtLocation.SetFocus
MsgBox "Please enter a Location"
Exit Sub
End If
'copy the data to the database
ws.Cells(iRow, 1).Value = Me.txtCal.Value
ws.Cells(iRow, 2).Value = Me.TextBoxDocNo.Value
ws.Cells(iRow, 3).Value = Me.txtDescription.Value
ws.Cells(iRow, 4).Value = Me.txtLocation.Value
ws.Cells(iRow, 5).Value = Me.ClassDoc.Value
ws.Cells(iRow, 6).Value = Me.ScheduleDoc.Value
ws.Cells(iRow, 10).Value = Me.edited.Value
ws.Cells(iRow, 11).Value = Me.unedited.Value
ws.Cells(iRow, 14).Value = Me.txtNotes.Value
'ws.Cells(iRow, 7).Value = Me.ClassDoc.Value
'ws.Cells(iRow, 5).Value = Me.ClassDoc.Value
If edited.Value = True Then Range("H" & iRow) = "*EDITED*"
If unedited.Value = True Then Range("I" & iRow) = "*UNEDITED*"
If TCPS.Value = True Then Range("G" & iRow) = "Test"
If Attached.Value = True Then Range("L" & iRow) = "*"
'If EditedMG6C = True Then [G4] = "MG6E"
'If EditedMG6C = False Then [G4] = ""
Me.TextBoxDocNo.SetFocus
Me.txtCal.SetFocus
Me.txtLocation.SetFocus
Me.txtDescription.SetFocus
Unload Me
frmDataEntry.Show
End Sub
Private Sub cmdClose_Click()
Unload Me
End Sub
Private Sub CommandButton3_Click()
Dim iRow As Long
Dim ws As Worksheet
Dim x As Integer
Dim bIncrement As Boolean
Set ws = Worksheets("Workings")
'find last data row from database
iRow = ws.Cells(Rows.Count, 12) _
.End(xlUp).Row
x = 0
Sheets("Workings").Select
Do
If IsEmpty(Range("L1").Offset(x + 1, 0).Value) Then
Range("L1").Offset(x + 1, 0).Value = Range("L1").Offset(x, 0) + 1
bIncrement = True
End If
x = x + 1
Loop Until bIncrement = True
Me.TextBoxDocNo.Text = "D" & ws.Cells(iRow, 12).Value + 1
Sheets("Documents").Select
End Sub
Private Sub UserForm_QueryClose(Cancel As Integer, _
CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
Cancel = True
MsgBox "Please use the Close Input Form Button!"
End If
End Sub
Private Sub cmd1_Click()
CalendarForm.Show
End Sub
Private Sub UserForm_Activate()
Dim iRow As Long
Dim ws As Worksheet
Dim x As Integer
Dim bIncrement As Boolean
Set ws = Worksheets("Workings")
'find last data row from database
iRow = ws.Cells(Rows.Count, 12) _
.End(xlUp).Row
x = 0
Sheets("Workings").Select
Do
If IsEmpty(Range("L1").Offset(x + 1, 0).Value) Then
Range("L1").Offset(x + 1, 0).Value = Range("L1").Offset(x, 0) + 1
bIncrement = True
End If
x = x + 1
Loop Until bIncrement = True
Me.TextBoxDocNo.Text = "D" & ws.Cells(iRow, 12).Value + 1
Sheets("Documents").Select
End SubWhat i am trying to achieve is auto numbering in column B in Documents with D1, D2, etc.
Each time the userform is opened or restarted or populated by a the command button it populates the D1, D2 etc textbox on the userform and then the form submits the alll the data to the next free row in the worksheet.
As you will see i have with my very limited knowledge created a work round utilising the workings worksheet to populate column L and then transfers this to the userform. This code relys on the cell above A2 to be a number.
Is it possible to dispense with my workround and get the userform to populate the textbox automatically as it reads from column B in the documents worksheet, collects the last D number used and allocates the next one, if the form is blank then it should allocate D1 in the first instance.
I do hope i have made some sense and that this last hurdle can be overcome, thank you in anticipation,
regards
Lee
Last edited by a moderator:
