Paste value from a Userform to a Excel Sheet

Question

I want to Paste values from an Excel Userform to a sheet. The values can be pasted into diffrent sheets depending on what you put inside of the Userform.

I have come this far:

Private Sub Lagginarenda_Click()
Sheets("KategoriComboBox").Range("B2").Value = TextBoxFragestallare.Value
End Sub

The KategoriComboBox is a Userform Dropdown list in which you can choose a name in. The same values that contains in that list have a similar Excel sheet.

The TextBoxFragestallare is a TextBox in which you can write in a value. This Value I want to paste inside of Cell B2 in a sheet that you also choose inside of the userform.

The Code wont work because it says "Index out of bound- Runtime error '9'"

---

I have managed to come this far:

Private Sub Lagginarende_Click()

Dim emptyRow As Long

'Aktiverar sheet
Sheets("Byggkonstruktion").Activate

'Determine emptyRow
emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1

'Överför information
Cells(emptyRow, 1).Value = TextBoxLopnummer.Value
Cells(emptyRow, 2).Value = TextBoxFragestallare.Value
Cells(emptyRow, 3).Value = TextBoxMottagare.Value
Cells(emptyRow, 4).Value = TextBoxDatum.Value
Cells(emptyRow, 5).Value = TextBoxFraga.Value
Cells(emptyRow, 8).Value = TextBoxSvar.Value
If KanBesvaraFraganJa.Value = True Then Cells(emptyRow, 6).Value = KanBesvaraFraganJa.Caption Else Cells(emptyRow, 6).Value = KanBesvaraFraganNej.Caption

Unload Me
End Sub

The only issue I have now is how can I instead of using Sheets("Byggkonstruktion").Activate use the value in a drop-down list that is in the userform?

Answer 1

The Sub below probably works for you. You should suitably qualify Ranges, and avoid using Select or Activate unless they are strictly needed.

Note that you should: 1) populate your ComboBox prior to using it, 2) make sure that the selected value in the ComboBox is what you want, so you can use KategoriComboBox.Value, 3) make sure that there exists the worksheet you are trying to use in the ActiveWorkbook, or choose it suitably.

Private Sub Lagginarende_Click()
    Dim emptyRow As Long

    'Aktiverar sheet
    Dim ws As Worksheet
    Set ws = ActiveWorkbook.Sheets(KategoriComboBox.Value)
    'Sheets("Byggkonstruktion").Activate

    'Determine emptyRow
    emptyRow = WorksheetFunction.CountA(ws.Range("A:A")) + 1
    'emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1

    'Överför information
    ws.Cells(emptyRow, 1).Value = TextBoxLopnummer.Value
    ws.Cells(emptyRow, 2).Value = TextBoxFragestallare.Value
    ws.Cells(emptyRow, 3).Value = TextBoxMottagare.Value
    ws.Cells(emptyRow, 4).Value = TextBoxDatum.Value
    ws.Cells(emptyRow, 5).Value = TextBoxFraga.Value
    ws.Cells(emptyRow, 8).Value = TextBoxSvar.Value
    If KanBesvaraFraganJa.Value = True Then ws.Cells(emptyRow, 6).Value = KanBesvaraFraganJa.Caption Else ws.Cells(emptyRow, 6).Value = KanBesvaraFraganNej.Caption

    Unload Me
End Sub

Answer 2

The Answer to my question is the following:

Private Sub KategoriComboBox_Change()

   Sheets(KategoriComboBox.Text).Activate

End Sub

What it does it that it activates the sheet that has the same name as the one you choose in the Combobox.

Answer 3

If I understand your question correctly, you want to let the user select a certain sheet name through a dropdown control and then paste the text they entered in this sheet in cell 'B2'.

So your setup might look something like this:

Here is how you can achieve this: (Suppose you have a ComboBox named cbxSheet, a TextBox named txbText and a CommandButton named btnCopyTextToSelectedSheet in a UserForm)

Option Explicit

Private Sub UserForm_Initialize()

    Dim wksCurrentSheet As Worksheet

    'Add all available sheet names to dropdown box
    For Each wksCurrentSheet In Worksheets
        cbxSheet.AddItem wksCurrentSheet.Name
    Next wksCurrentSheet

End Sub

Private Sub btnCopyTextToSelectedSheet_Click()

    Dim strText As String
    Dim strSheetName As String
    Dim wksDestination As Worksheet

    'Read sheet name from dropdown box
    strSheetName = cbxSheet.Value

    'Try to get sheet with the defined name
    Set wksDestination = Worksheets(strSheetName)
    'If there is no sheet with this name you will receive
    'an 'Index out of bound' (9) runtime error

    'Get text from textbox
    strText = txbText.Text

    'Write to cell in destination worksheet
    wksDestination.Activate     'Not needed, just to let the user see
                            'that the copying really happens :)
    wksDestination.Range("B2").Value = strText

    'Unload form (makes sure the UserForm_Initialize sub is called on
    '             each use of the form)
    Unload Me

End Sub

I uploaded the sample here: https://dl.dropboxusercontent.com/u/40951326/SheetSelectionExample.xlsm

Hope this gives you an idea on how to achieve what you want!

Answer 4

It appears you just want:

Private Sub Lagginarenda_Click()
Sheets(KategoriComboBox.Value).Range("B2").Value = TextBoxFragestallare.Value
End Sub