sspatriots
Well-known Member
- Joined
- Nov 22, 2011
- Messages
- 565
- Office Version
- 365
- Platform
- Windows
Hi,
This is the other code that I was working on. I need to be able to stop this code if the cell directly to the left of the one that is found for the row I'm populating is blank. Maybe give the user a pop-up message that says "There are no more allocated Job Numbers available. Please have additional Job numbers allocated.". Then I want it to close the form when they select OK.
This code currently finds the first empty cell in column B and allows the user to enter the information that populates columns B, C & D. However, if there is no allocated number in column A, I want to stop the user from going further as explained above. Thanks in advance for any help on this.
This is the other code that I was working on. I need to be able to stop this code if the cell directly to the left of the one that is found for the row I'm populating is blank. Maybe give the user a pop-up message that says "There are no more allocated Job Numbers available. Please have additional Job numbers allocated.". Then I want it to close the form when they select OK.
This code currently finds the first empty cell in column B and allows the user to enter the information that populates columns B, C & D. However, if there is no allocated number in column A, I want to stop the user from going further as explained above. Thanks in advance for any help on this.
VBA Code:
Private Sub CommandButton2_Click()
Dim ws As Worksheet
Dim LO As ListObject
Dim Lastrow As Integer
Dim C As Range
Set LO = Sheet1.ListObjects("Table1")
Set ws = ThisWorkbook.Sheets("Sheet1")
With LO.Range.Columns(2) 'column_to_check is relative to the LO.Range
Set C = .Find(what:="*", after:=.Cells(1), LookIn:=xlValues, _
searchorder:=xlByRows, searchdirection:=xlPrevious)
If Not C Is Nothing Then
'do stuff`
Debug.Print C.Row + 1 'last empty row
'If the row is the last in the table, then column is full
End If
End With
With ws
Lastrow = C.Row + 1
.Cells(Lastrow, 2).Value = TextBox2.Text
.Cells(Lastrow, 3).Value = TextBox3.Text
.Cells(Lastrow, 4).Value = TextBox4.Text
End With
TextBox2.Value = ""
TextBox3.Value = ""
TextBox4.Value = ""
End Sub