VBA Code to fill group of data

[NDMDRB]

Hello,

I have a userform "PC" to manage the PCs usage and timing
I have 8 PCs in each room, sometimes each one (or some of them) used for different type/time... and sometimes all of them for the same type/time...

In the userform "PC" I have the following controls
1- Combo Box "cmb_Num" (List from 1 to 8, to determine the number of users)
2- Text Boxes "txt_Name1 / txt_Name2 / txt_Name3.... until 8" (to fill the user's names)
3- Combo Box "cmb_Type" (list of usage types: research, Data Entry, Printing...)
4- Text Box "txt_Time" (to determine how long the user will use the PC: 30 Min, 1 Hour...)
5- There are many other textboxes, but no need to add all of them
6- Command Button "cmd_Add"

For now, If I will book for one user only, I have the following code

Private Sub cmd_Add_Click()
Dim ws As Worksheet
Set ws = Sheet15

If cmb_Num = "1" Then
nr = ws.Cells(Rows.Count, "A").End(xlUp).Row + 1
ws.Cells(nr, "A") = CDbl(ws.Cells(Rows.Count, 1).End(xlUp).value + 1)
ws.Cells(nr, "B") = Me.txt_Name1
ws.Cells(nr, "C") = Me.cmb_Type
ws.Cells(nr, "C") = Me.txt_Time
'And the rest of values
End If
End Sub

So, the above code is works great for one user booking

But, let's say 8 users will use 8 PCs for the same type and the same time....
I know the hard way that will be so difficult and huge code, which is:

If cmb_Num = "1" Then
'Enter the above code

If cmb_Num = "2" Then
'Enter the above code
'And again the same code (but instead of [ws.Cells(nr, "B") = Me.txt_Name1] use this [ws.Cells(nr, "B") = Me.txt_Name2]

If cmb_Num = "3" Then
'Enter the above code
'And again the same code (but instead of [ws.Cells(nr, "B") = Me.txt_Name1] use this [ws.Cells(nr, "B") = Me.txt_Name2]
'And again the same code (but instead of [ws.Cells(nr, "B") = Me.txt_Name1] use this [ws.Cells(nr, "B") = Me.txt_Name3]

.... Until 8

Is there any easy way to manage this:

• If there is one user, just fill the first empty row by the given data
• If there are 2 users, fill the first empty row by the given data, and the next row with same data unless the name (use txt_Name2)
• Same for 3 users, 4, 5...8

Thanks in advanced

 

[Fluff]

How about

Private Sub cmd_Add_Click()
    Dim ws As Worksheet
    Dim nr As Long, i As Long
    Set ws = Sheet9
    
    nr = ws.Cells(Rows.Count, "A").End(xlUp).Row
    For i = 1 To cmb_Num.Value
        ws.Cells(nr, "A").Offset(i) = nr + i
        ws.Cells(nr, "B").Offset(i) = Me.Controls("txt_Name" & i)
        ws.Cells(nr, "C").Offset(i) = Me.cmb_Type
        ws.Cells(nr, "C").Offset(i) = Me.txt_Time
    Next i
End Sub

 

[NDMDRB]

Amazing Fluff, thank you so much

 

[Fluff]

You're welcome & thanks for the feedback

 

[NDMDRB]

Hello Fluff, how are today,

If I may ask for one more favor regarding the same code

In my previous code I added the following lines at the beginning to check
If Me.txt_Name1 = "" Then
Me.lbl_Alert.Caption = "The Name field for user1 is empty!!!"
Me.txt_Name1.SetFocus
Exit Sub
End If


Can you please help me how to make it easy for group?

If cmb_Num = 1
Just check the first txt_Name if = "" then
Me.lbl_Alert.Caption = "The Name field for user1 is empty!!!"
Me.txt_Name1.SetFocus
Exit Sub
End If

If cmb_Num = 2
* Check txt_Name1 if = "" then
Me.lbl_Alert.Caption = "The Name field for user1 is empty!!!"
Me.txt_Name1.SetFocus
* Check txt_Name2 if = "" then
Me.lbl_Alert.Caption = "The Name field for user2 is empty!!!"
Me.txt_Name1.SetFocus

...

Exit Sub
End If


If the msg above can't be done (User1 or User2...) Just make it (You should add all the name fields) or anything else


Many thanks in advanced

 

[Fluff]

How about

Private Sub cmd_Add_Click()
    Dim ws As Worksheet
    Dim nr As Long, i As Long
    Set ws = Sheet15
    
    nr = ws.Cells(Rows.Count, "A").End(xlUp).Row
    For i = 1 To cmb_Num.Value
        If Me.Controls("txt_Name" & i) = "" Then
            Me.lbl_Alert.Caption = "The name field for User" & i & "is empty"
            Me.Controls("txt_Name" & i).SetFocus
            Exit Sub
        End If
        ws.Cells(nr, "A").Offset(i) = nr + i
        ws.Cells(nr, "B").Offset(i) = Me.Controls("txt_Name" & i)
        ws.Cells(nr, "C").Offset(i) = Me.cmb_Type
        ws.Cells(nr, "C").Offset(i) = Me.txt_Time
    Next i
End Sub

 

[NDMDRB]

With this code, in case cmb_Num = 2
If I added the first name but the second still null
it will add one line with the first name and the related data

But I need it in case cmb_Num = 4 and in case any of the txt_name (1,2,3 or 4 is null, exit sub and the setfocus for the null text)

 

[Fluff]

In that case, how about

Private Sub cmd_Add_Click()
    Dim ws As Worksheet
    Dim nr As Long, i As Long
    Set ws = Sheet15
    
    nr = ws.Cells(Rows.Count, "A").End(xlUp).Row
    For i = 1 To cmb_Num.Value
        If Me.Controls("txt_Name" & i) = "" Then
            Me.lbl_Alert.Caption = "The name field for User" & i & "is empty"
            Me.Controls("txt_Name" & i).SetFocus
            Exit Sub
        End If
    Next i
    For i = 1 To cmb_Num.Value
        ws.Cells(nr, "A").Offset(i) = nr + i
        ws.Cells(nr, "B").Offset(i) = Me.Controls("txt_Name" & i)
        ws.Cells(nr, "C").Offset(i) = Me.cmb_Type
        ws.Cells(nr, "C").Offset(i) = Me.txt_Time
    Next i
End Sub

 

[NDMDRB]

Hello Fluff, how are you today,

Back again with another inquiry if I may ask for.

I'm trying to add a piece of code to check if any name is duplicate, I tried the following code but didn't work

Can you please help me?

[/COLOR]    For i = 1 To cmb_Num.value
    If Me.Controls("txt_Name" & i) = Me.Controls("txt_Name" & i) Then
    Me.lbl_Alert.Caption = "The name of User " & i & " is already exist!!!"
    Me.Controls("txt_Name" & i).SetFocus
    Exit Sub
    End If
    Next i
[COLOR=#333333]

 

[Fluff]

As this is now a totally different question, you will need to start a new thread, thanks.