UserForm in a Specific range

[JessyVal]

Hi guys, I hope someone Can help with the following:

Have created a table that is filled using a form. I would like to specify that the form can have a range of 29 entries. Because I can only have 29 beds, as the time going pass, when patients are discharged i can delete an entry manually. Then the next tim ethe form is completed it needs to add the new entry to this empty row.

For example, I don't have a registered user in row 2, with the user form I need to put the next data into that row. I have this code in VBA

Private Sub CommandButtonSave_Click()

Dim fill As Long

Sheets("Ward_Planner").Activate
fill = WorksheetFunction.CountA(Range("A:A")) + 1

Cells(fill, 1).Value = ComboBoxBed.Value
Cells(fill, 2).Value = TextBoxName.Value
Cells(fill, 3).Value = ComboBoxConsultant.Value
Cells(fill, 4).Value = TextBoxPcn.Value
Cells(fill, 5).Value = TextBoxDoa.Value
Cells(fill, 6).Value = ComboBoxGender.Value
Cells(fill, 7).Value = ComboBoxStatus.Value
Cells(fill, 8).Value = ComboBoxDiet.Value
Cells(fill, 9).Value = TextBoxComments.Value

Unload Me
End Sub

 

[Fluff]

How about

   Dim fill As Long

   Sheets("Ward_Planner").Activate
   On Error Resume Next
   fill = Range("A2:A29").SpecialCells(xlBlanks)(1).Row
   On Error GoTo 0
   If fill = 0 Then
      MsgBox "all beds are filled"
      Exit Sub
   End If
   Cells(fill, 1).Value = ComboBoxBed.Value
   Cells(fill, 2).Value = TextBoxName.Value

 

[JessyVal]

How about

   Dim fill As Long

   Sheets("Ward_Planner").Activate
   On Error Resume Next
   fill = Range("A2:A29").SpecialCells(xlBlanks)(1).Row
   On Error GoTo 0
   If fill = 0 Then
      MsgBox "all beds are filled"
      Exit Sub
   End If
   Cells(fill, 1).Value = ComboBoxBed.Value
   Cells(fill, 2).Value = TextBoxName.Value

Thank you so sooooooo much! it works perfect!

 

[Fluff]

You're welcome & thanks for the feedback.