vba 将用户表单中的值粘贴到 Excel 工作表

Paste value from a Userform to a Excel Sheet

提问by Joneskvist

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.

我想将 Excel 用户表单中的值粘贴到工作表中。根据您在用户表单中放置的内容，可以将值粘贴到不同的表中。

I have come this far:

我已经走到这一步了：

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

The KategoriComboBoxis 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.

该KategoriComboBox是一个窗体下拉列表中，你可以选择一个名字。包含在该列表中同样的值也有类似的Excel工作表。

The TextBoxFragestallareis 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.

该TextBoxFragestallare是，你可以在一个值写入一个文本框。我想将此值粘贴到单元格 B2 内的工作表中，您也在用户表单内选择该工作表。

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

该代码将不起作用，因为它显示“索引超出范围 - 运行时错误 '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").Activateuse the value in a drop-down list that is in the userform?

我现在唯一的问题是如何而不是使用Sheets("Byggkonstruktion").Activate使用用户表单中下拉列表中的值？

回答by Marcus Mangelsdorf

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'.

如果我正确理解您的问题，您希望让用户通过下拉控件选择某个工作表名称，然后将他们在此工作表中输入的文本粘贴到单元格“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)

以下是实现此目的的方法：（假设在 UserForm 中有一个名为 cbxSheet 的 ComboBox、一个名为 txbText 的 TextBox 和一个名为 btnCopyTextToSelectedSheet 的 CommandButton）

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

我在这里上传了示例：https: //dl.dropboxusercontent.com/u/40951326/SheetSelectionExample.xlsm

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

希望这能让您了解如何实现您想要的！

回答by sancho.s ReinstateMonicaCellio

The Subbelow probably works for you. You should suitably qualify Ranges, and avoid using Selector Activateunless they are strictly needed.

在Sub下面可能为你工作。您应该适当地限定Ranges，并避免使用SelectorActivate除非它们是严格需要的。

Note that you should: 1) populate your ComboBoxprior to using it, 2) make sure that the selected value in the ComboBoxis 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.

请注意，您应该：1）ComboBox在使用之前填充它，2）确保在 中选择的值ComboBox是您想要的，以便您可以使用KategoriComboBox.Value，3）确保存在您尝试使用的工作表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

回答by Joneskvist

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.

它的作用是激活与您在组合框中选择的名称相同的工作表。

回答by Rory

It appears you just want:

看来你只是想要：

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