Excel – Paste value from a Userform to a Excel Sheet

excelvba

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?

Best Answer

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: Demo application for pasting on selected sheet

Enter text

Text is pasted on selected sheet

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!

Related Topic