VBA - Allows the user to select the sheet where to paste values

[Danoob]

Hi all,

I'm trying to put a combobox or something in a userform to allow the user to select the sheet where he wants to paste the values he put in the form. As of right now, I modified the sheet manually which isn't ideal.
The closest thing I saw to what I'm looking for is in this thread : VBA - Allow User to Select Which Worksheet to Copy but it's for copying and I don't know how to adapt it to the code below.
The following code is only execute once the user filled the forms with his data and click the "Add" button. Also to precise, all sheets have the same presentation.

Thanks to anyone that can help me or provide ideas.

 Private Sub BtnAdd_Click()

    Sheets("DATA 20220516").Activate 'Sheet where the data is added
    Range("A" & Rows.Count).End(xlUp).Offset(1).Select
    'Positionnement sur la dernière ligne
    'Décalage d'une ligne vers le bas
    ActiveCell = Txtid.Value
    ActiveCell.Offset(0, 1).Value = cmbsoc
    ActiveCell.Offset(0, 2).Value = TxtCV
    ActiveCell.Offset(0, 3).Value = Cmbdiv
    ActiveCell.Offset(0, 4).Value = CmbVend
    ActiveCell.Offset(0, 5).Value = Txtref
    ActiveCell.Offset(0, 6).Value = Txtpur
    ActiveCell.Offset(0, 8).Value = Txtam
    ActiveCell.Offset(0, 9).Value = txtdtd
    ActiveCell.Offset(0, 10).Value = txtPdate
    ActiveCell.Offset(0, 16).Value = CmbPO
    ActiveCell.Offset(0, 18).Value = Txtcomm
    ActiveCell.Offset(0, 19).Value = TxtCons
    ActiveCell.Offset(0, 20).Value = Txtact
    ActiveCell.Offset(0, 21).Value = TxtRes
    ActiveCell.Offset(0, 22).Value = Txtissu
    
    MsgBox "Nouvelle entrée prise en compte", vbOKOnly + vbInformation, "CONFIRMATION"
    
End Sub

 

[dmt32]

Hi,
untested but try this update to your code & see if does what you want

Place combobox on your userforform & name it cbSheetNames

 Private Sub BtnAdd_Click()
    Dim ws  As Worksheet
    Dim rng As Range
    
    Set ws = ThisWorkbook.Worksheets(Me.cbSheetNames.Value)
    Set rng = ws.Range("A" & ws.Rows.Count).End(xlUp).Offset(1)
    
    'Positionnement sur la dernière ligne
    'Décalage d'une ligne vers le bas
    rng = Txtid.Value
    rng.Offset(0, 1).Value = cmbsoc
    rng.Offset(0, 2).Value = TxtCV
    rng.Offset(0, 3).Value = Cmbdiv
    rng.Offset(0, 4).Value = CmbVend
    rng.Offset(0, 5).Value = Txtref
    rng.Offset(0, 6).Value = Txtpur
    rng.Offset(0, 8).Value = Txtam
    rng.Offset(0, 9).Value = txtdtd
    rng.Offset(0, 10).Value = txtPdate
    rng.Offset(0, 16).Value = CmbPO
    rng.Offset(0, 18).Value = Txtcomm
    rng.Offset(0, 19).Value = TxtCons
    rng.Offset(0, 20).Value = Txtact
    rng.Offset(0, 21).Value = TxtRes
    rng.Offset(0, 22).Value = Txtissu
    
    MsgBox "Nouvelle entrée prise en compte", vbOKOnly + vbInformation, "CONFIRMATION"
    
End Sub

Private Sub UserForm_Initialize()
    Dim ws As Worksheet
    With Me.cbSheetNames
        For Each ws In ThisWorkbook.Worksheets
            .AddItem ws.Name
        Next ws
        .Style = fmStyleDropDownList
        .ListIndex = 0
    End With
End Sub

Dave

 

[Danoob]

Hi,
untested but try this update to your code & see if does what you want

Place combobox on your userforform & name it cbSheetNames

 Private Sub BtnAdd_Click()
    Dim ws  As Worksheet
    Dim rng As Range
   
    Set ws = ThisWorkbook.Worksheets(Me.cbSheetNames.Value)
    Set rng = ws.Range("A" & ws.Rows.Count).End(xlUp).Offset(1)
   
    'Positionnement sur la dernière ligne
    'Décalage d'une ligne vers le bas
    rng = Txtid.Value
    rng.Offset(0, 1).Value = cmbsoc
    rng.Offset(0, 2).Value = TxtCV
    rng.Offset(0, 3).Value = Cmbdiv
    rng.Offset(0, 4).Value = CmbVend
    rng.Offset(0, 5).Value = Txtref
    rng.Offset(0, 6).Value = Txtpur
    rng.Offset(0, 8).Value = Txtam
    rng.Offset(0, 9).Value = txtdtd
    rng.Offset(0, 10).Value = txtPdate
    rng.Offset(0, 16).Value = CmbPO
    rng.Offset(0, 18).Value = Txtcomm
    rng.Offset(0, 19).Value = TxtCons
    rng.Offset(0, 20).Value = Txtact
    rng.Offset(0, 21).Value = TxtRes
    rng.Offset(0, 22).Value = Txtissu
   
    MsgBox "Nouvelle entrée prise en compte", vbOKOnly + vbInformation, "CONFIRMATION"
   
End Sub

Private Sub UserForm_Initialize()
    Dim ws As Worksheet
    With Me.cbSheetNames
        For Each ws In ThisWorkbook.Worksheets
            .AddItem ws.Name
        Next ws
        .Style = fmStyleDropDownList
        .ListIndex = 0
    End With
End Sub

Dave

I did what you said and it works perfectly, thanks you a lot for your help Dave

 

[dmt32]

I did what you said and it works perfectly, thanks you a lot for your help Dave

most welcome & appreciate your feedback

Dave