在组合框中选择名称时，VBA用户窗体将textbox.Value保存到excel工作表 [英] VBA Userform to save textbox.Value to excel worksheet when name is selected in combobox

问题描述

对于发布相同的问题，我事先表示歉意，但是我不知道如何添加其他代码示例。如果有方法可以将其他代码添加到上一个问题，请告知。基本上，我试图将一些文本框值保存到我的工作表中，以便在关闭和重新打开用户窗体时可以重新启动它们。到目前为止，这就是我所拥有的..但显然是错误的！




基本上，我有一个combobox（procNamecombobox），它填充在工作表 DailyNumbers上的 A列中。我只希望下面的textboxe.Value在组合框中选中时，将其保存在每个名称旁边的相应列（B，C，D和E）中。

I apologize in advance for posting the same question, but I don't know how else to add additional code example. If there is a way to add additional code to a previous question, please advise. Basically, I am trying to save some textbox values into my worksheet so they can be reinitiated when userform is closed and re-opened. This is what I have thus far..but clearly its wrong!

Basicaly, I have a combobox(procNamecombobox) that populates from column "A" on worksheet "DailyNumbers". I just want the below textboxe.Values to save in the corresponding columns (B,C,D & E) next to each name, when its selected in the combobox.

  Private Sub procNamecombobox_Change()    

  Dim ws As Worksheet
  Dim EmptyRow As Long

  Set ws = Sheets("DailyNumbers")
  EmptyRow = ws.Range("B" & Rows.Count).End(xlUp).Row + 1
  ' *** Check combobox selection ***
  If procNamecombobox.ListIndex > -1 Then

  ws.Range("B" & EmptyRow).Value = completeCount.Text
  ws.Range("C" & EmptyRow).Value = handledCount.Text
  ws.Range("D" & EmptyRow).Value = wipCount.Text
  ws.Range("E" & EmptyRow).Value = suspendCount.Text
  ws.Range("B2:B" & EmptyRow).Sort key1:=ws.Range("A1:A" & EmptyRow),  order1:=xlAscending, Header:=xlNo
  Else
 MsgBox "Please select your name"
 End If

  End Sub

推荐答案

接下来，它将在A列中搜索Combobox值，如果找到它将更新该行，如果找不到，则会填充EmptyRow：

How about the following, it will search Column A for the Combobox value, if found it will update that row, if not found, it will populate the EmptyRow:

Private Sub procNamecombobox_Change()
Dim ws As Worksheet: Set ws = Sheets("DailyNumbers")
Dim EmptyRow As Long
Dim FoundVal As Range
EmptyRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row + 1
' *** Check combobox selection ***
    If procNamecombobox.ListIndex > -1 Then
        Set FoundVal = ws.Range("A1:A" & EmptyRow).Find(procNamecombobox.Value) 'find Combobox value in Column A
        If Not FoundVal Is Nothing Then 'if found
            ws.Range("B" & FoundVal.Row).Value = completeCount.Text 'use that row to populate cells
            ws.Range("C" & FoundVal.Row).Value = handledCount.Text
            ws.Range("D" & FoundVal.Row).Value = wipCount.Text
            ws.Range("E" & FoundVal.Row).Value = suspendCount.Text
        Else 'if not found use EmptyRow to populate Cells
            ws.Range("A" & EmptyRow).Value = procNamecombobox.Value
            ws.Range("B" & EmptyRow).Value = completeCount.Text
            ws.Range("C" & EmptyRow).Value = handledCount.Text
            ws.Range("D" & EmptyRow).Value = wipCount.Text
            ws.Range("E" & EmptyRow).Value = suspendCount.Text
        End If
    Else
        MsgBox "Please select your name"
    End If
End Sub

这篇关于在组合框中选择名称时，VBA用户窗体将textbox.Value保存到excel工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！