时间戳

首页 > 解决方案 > 如何在电子表格单元格中显示行号以从 activex 组合框中进行选择？

excel - 如何在电子表格单元格中显示行号以从 activex 组合框中进行选择？

问题描述

我有一个工作表，用于更新表中的现有条目，并使用下拉列表选择要更新的条目，并使用宏将条目写回数据表。最初我使用了一个列表框并获得了我想要的值作为“单元格链接”，但是我需要将其更改为 ActiveX 组合框，以便列表在下拉列表中显示两列，姓氏和教名，以启用选择姓氏相同但教名不同的人。但是，通过使用组合框，我丢失了宏需要的行号。我需要一个 Excel 电子表格单元格，其中包含所选值的行号，以便我的宏知道在哪里输入更新的条目。我努力了=MATCH(B3,Data!A3:A99,0)但这仅显示姓氏的第一次出现。我找不到可以更改的组合框的属性，并且已经搜索并搜索了互联网，但没有找到我理解的任何内容。谢谢

没有代码可看

Private Sub ComboBox1_Change()

End Sub.

宏代码为：

Sub amend_table()

'Written by Keith Cooper 10/10/2021

Dim NewRow As Integer
NewRow = Worksheets("amend").Range("G1").Value
'If Worksheets("input").Range("I1").Value <> 0 Then
'MsgBox "There are errors. No data has been added!", vbOKOnly, "Warning!"
'Exit Sub
'End If

Worksheets("Data").Cells(NewRow, 1).Value = Worksheets("amend").Range("B3").Value
Worksheets("Data").Cells(NewRow, 2).Value = Worksheets("amend").Range("B4").Value
Worksheets("Data").Cells(NewRow, 3).Value = Worksheets("amend").Range("D3").Value
Worksheets("Data").Cells(NewRow, 4).Value = Worksheets("amend").Range("B5").Value
Worksheets("Data").Cells(NewRow, 5).Value = Worksheets("amend").Range("D5").Value
Worksheets("Data").Cells(NewRow, 6).Value = Worksheets("amend").Range("B7").Value
Worksheets("Data").Cells(NewRow, 7).Value = Worksheets("amend").Range("B8").Value
Worksheets("Data").Cells(NewRow, 8).Value = Worksheets("amend").Range("B9").Value
Worksheets("Data").Cells(NewRow, 9).Value = Worksheets("amend").Range("B10").Value
Worksheets("Data").Cells(NewRow, 10).Value = Worksheets("amend").Range("B11").Value
Worksheets("Data").Cells(NewRow, 11).Value = Worksheets("amend").Range("C12").Value
Worksheets("Data").Cells(NewRow, 12).Value = Worksheets("amend").Range("C13").Value
Worksheets("Data").Cells(NewRow, 13).Value = Worksheets("amend").Range("C14").Value
Worksheets("Data").Cells(NewRow, 14).Value = Worksheets("amend").Range("B15").Value
Worksheets("Data").Cells(NewRow, 15).Value = Worksheets("amend").Range("B17").Value
Worksheets("Data").Cells(NewRow, 16).Value = Worksheets("amend").Range("B18").Value
Worksheets("Data").Cells(NewRow, 17).Value = Worksheets("amend").Range("B19").Value
Worksheets("Data").Cells(NewRow, 18).Value = Worksheets("amend").Range("B20").Value
Worksheets("Data").Cells(NewRow, 19).Value = Worksheets("amend").Range("B22").Value
Worksheets("Data").Cells(NewRow, 20).Value = Worksheets("amend").Range("B23").Value
Worksheets("Data").Cells(NewRow, 21).Value = Worksheets("amend").Range("B24").Value
Worksheets("Data").Cells(NewRow, 22).Value = Worksheets("amend").Range("B25").Value
Worksheets("Data").Cells(NewRow, 23).Value = Worksheets("amend").Range("B26").Value
Worksheets("Data").Cells(NewRow, 24).Value = Worksheets("amend").Range("B27").Value
Worksheets("Data").Cells(NewRow, 25).Value = Worksheets("amend").Range("B28").Value
Worksheets("Data").Cells(NewRow, 26).Value = Worksheets("amend").Range("B29").Value
Worksheets("Data").Cells(NewRow, 27).Value = Worksheets("amend").Range("E3").Value
Worksheets("Data").Cells(NewRow, 28).Value = Worksheets("amend").Range("F3").Value
Worksheets("Data").Cells(NewRow, 29).Value = Worksheets("amend").Range("E4").Value
Worksheets("Data").Cells(NewRow, 30).Value = Worksheets("amend").Range("F4").Value
Worksheets("Data").Cells(NewRow, 31).Value = Worksheets("amend").Range("E5").Value
Worksheets("Data").Cells(NewRow, 32).Value = Worksheets("amend").Range("F5").Value
Worksheets("Data").Cells(NewRow, 33).Value = Worksheets("amend").Range("E6").Value
Worksheets("Data").Cells(NewRow, 34).Value = Worksheets("amend").Range("F6").Value
Worksheets("Data").Cells(NewRow, 35).Value = Worksheets("amend").Range("E7").Value
Worksheets("Data").Cells(NewRow, 36).Value = Worksheets("amend").Range("F7").Value
Worksheets("Data").Cells(NewRow, 37).Value = Worksheets("amend").Range("E8").Value
Worksheets("Data").Cells(NewRow, 38).Value = Worksheets("amend").Range("F8").Value
Worksheets("Data").Cells(NewRow, 39).Value = Worksheets("amend").Range("E9").Value
Worksheets("Data").Cells(NewRow, 40).Value = Worksheets("amend").Range("F9").Value
Worksheets("Data").Cells(NewRow, 41).Value = Worksheets("amend").Range("E10").Value
Worksheets("Data").Cells(NewRow, 42).Value = Worksheets("amend").Range("F10").Value
Worksheets("Data").Cells(NewRow, 43).Value = Worksheets("amend").Range("E11").Value
Worksheets("Data").Cells(NewRow, 44).Value = Worksheets("amend").Range("F11").Value
Worksheets("Data").Cells(NewRow, 45).Value = Worksheets("amend").Range("E12").Value
Worksheets("Data").Cells(NewRow, 46).Value = Worksheets("amend").Range("F12").Value
Worksheets("Data").Cells(NewRow, 47).Value = Worksheets("amend").Range("E13").Value
Worksheets("Data").Cells(NewRow, 48).Value = Worksheets("amend").Range("F13").Value
Worksheets("Data").Cells(NewRow, 49).Value = Worksheets("amend").Range("E14").Value
Worksheets("Data").Cells(NewRow, 50).Value = Worksheets("amend").Range("F14").Value
Worksheets("Data").Cells(NewRow, 51).Value = Worksheets("amend").Range("E15").Value
Worksheets("Data").Cells(NewRow, 52).Value = Worksheets("amend").Range("F15").Value
Worksheets("Data").Cells(NewRow, 53).Value = Worksheets("amend").Range("E16").Value
Worksheets("Data").Cells(NewRow, 54).Value = Worksheets("amend").Range("F16").Value
Worksheets("Data").Cells(NewRow, 55).Value = Worksheets("amend").Range("E17").Value
Worksheets("Data").Cells(NewRow, 56).Value = Worksheets("amend").Range("F17").Value
Worksheets("Data").Cells(NewRow, 57).Value = Worksheets("amend").Range("E18").Value
Worksheets("Data").Cells(NewRow, 58).Value = Worksheets("amend").Range("F18").Value
Worksheets("Data").Cells(NewRow, 59).Value = Worksheets("amend").Range("E19").Value
Worksheets("Data").Cells(NewRow, 60).Value = Worksheets("amend").Range("F19").Value
Worksheets("Data").Cells(NewRow, 61).Value = Worksheets("amend").Range("E20").Value
Worksheets("Data").Cells(NewRow, 62).Value = Worksheets("amend").Range("F20").Value
Worksheets("Data").Cells(NewRow, 63).Value = Worksheets("amend").Range("E21").Value
Worksheets("Data").Cells(NewRow, 64).Value = Worksheets("amend").Range("F21").Value
Worksheets("Data").Cells(NewRow, 65).Value = Worksheets("amend").Range("E22").Value
Worksheets("Data").Cells(NewRow, 66).Value = Worksheets("amend").Range("F22").Value



Sheets("Amend").Select
           'Range("B3").Formula = "=INDEX(Data!A3:A100,G2)"
           Range("B4").Formula = "=IF(VLOOKUP($B$3,Data,2,FALSE)="""","""",VLOOKUP($B$3,Data,2,FALSE))"
           Range("D3").Formula = "=IF(VLOOKUP($B$3,Data,3,FALSE)="""","""",VLOOKUP($B$3,Data,3,FALSE))"
           Range("B5").Formula = "=IF(VLOOKUP($B$3,Data,4,FALSE)="""","""",VLOOKUP($B$3,Data,4,FALSE))"
           Range("D5").Formula = "=IF(VLOOKUP($B$3,Data,5,FALSE)="""","""",VLOOKUP($B$3,Data,5,FALSE))"
           Range("B7").Formula = "=IF(VLOOKUP($B$3,Data,6,FALSE)="""","""",VLOOKUP($B$3,Data,6,FALSE))"
           Range("B8").Formula = "=IF(VLOOKUP($B$3,Data,7,FALSE)="""","""",VLOOKUP($B$3,Data,7,FALSE))"
           Range("B9").Formula = "=IF(VLOOKUP($B$3,Data,8,FALSE)="""","""",VLOOKUP($B$3,Data,8,FALSE))"
           Range("B10").Formula = "=IF(VLOOKUP($B$3,Data,9,FALSE)="""","""",VLOOKUP($B$3,Data,9,FALSE))"
           Range("B11").Formula = "=IF(VLOOKUP($B$3,Data,10,FALSE)="""","""",VLOOKUP($B$3,Data,10,FALSE))"
           Range("C12").Formula = "=IF(VLOOKUP($B$3,Data,11,FALSE)="""","""",VLOOKUP($B$3,Data,11,FALSE))"
           Range("C13").Formula = "=IF(VLOOKUP($B$3,Data,12,FALSE)="""","""",VLOOKUP($B$3,Data,12,FALSE))"
           Range("C14").Formula = "=IF(VLOOKUP($B$3,Data,13,FALSE)="""","""",VLOOKUP($B$3,Data,13,FALSE))"
           Range("B15").Formula = "=IF(VLOOKUP($B$3,Data,14,FALSE)="""","""",VLOOKUP($B$3,Data,14,FALSE))"
           Range("B17").Formula = "=IF(VLOOKUP($B$3,Data,15,FALSE)="""","""",VLOOKUP($B$3,Data,15,FALSE))"
           Range("B18").Formula = "=IF(VLOOKUP($B$3,Data,16,FALSE)="""","""",VLOOKUP($B$3,Data,16,FALSE))"
           Range("B19").Formula = "=IF(VLOOKUP($B$3,Data,17,FALSE)="""","""",VLOOKUP($B$3,Data,17,FALSE))"
           Range("B20").Formula = "=IF(VLOOKUP($B$3,Data,18,FALSE)="""","""",VLOOKUP($B$3,Data,18,FALSE))"
           Range("B22").Formula = "=IF(VLOOKUP($B$3,Data,19,FALSE)="""","""",VLOOKUP($B$3,Data,19,FALSE))"
           Range("B23").Formula = "=IF(VLOOKUP($B$3,Data,20,FALSE)="""","""",VLOOKUP($B$3,Data,20,FALSE))"
           Range("B24").Formula = "=IF(VLOOKUP($B$3,Data,21,FALSE)="""","""",VLOOKUP($B$3,Data,21,FALSE))"
           Range("B25").Formula = "=IF(VLOOKUP($B$3,Data,22,FALSE)="""","""",VLOOKUP($B$3,Data,22,FALSE))"
           Range("B26").Formula = "=IF(VLOOKUP($B$3,Data,23,FALSE)="""","""",VLOOKUP($B$3,Data,23,FALSE))"
           Range("B27").Formula = "=IF(VLOOKUP($B$3,Data,24,FALSE)="""","""",VLOOKUP($B$3,Data,24,FALSE))"
           Range("B28").Formula = "=IF(VLOOKUP($B$3,Data,25,FALSE)="""","""",VLOOKUP($B$3,Data,25,FALSE))"
           Range("B29").Formula = "=IF(VLOOKUP($B$3,Data,26,FALSE)="""","""",VLOOKUP($B$3,Data,26,FALSE))"
           Range("E3").Formula = "=IF(VLOOKUP($B$3,Data,27,FALSE)="""","""",VLOOKUP($B$3,Data,27,FALSE))"
           Range("F3").Formula = "=IF(VLOOKUP($B$3,Data,28,FALSE)="""","""",VLOOKUP($B$3,Data,28,FALSE))"
           Range("E4").Formula = "=IF(VLOOKUP($B$3,Data,29,FALSE)="""","""",VLOOKUP($B$3,Data,29,FALSE))"
           Range("F4").Formula = "=IF(VLOOKUP($B$3,Data,30,FALSE)="""","""",VLOOKUP($B$3,Data,30,FALSE))"
           Range("E5").Formula = "=IF(VLOOKUP($B$3,Data,31,FALSE)="""","""",VLOOKUP($B$3,Data,31,FALSE))"
           Range("F5").Formula = "=IF(VLOOKUP($B$3,Data,32,FALSE)="""","""",VLOOKUP($B$3,Data,32,FALSE))"
           Range("E6").Formula = "=IF(VLOOKUP($B$3,Data,33,FALSE)="""","""",VLOOKUP($B$3,Data,33,FALSE))"
           Range("F6").Formula = "=IF(VLOOKUP($B$3,Data,34,FALSE)="""","""",VLOOKUP($B$3,Data,34,FALSE))"
           Range("E7").Formula = "=IF(VLOOKUP($B$3,Data,35,FALSE)="""","""",VLOOKUP($B$3,Data,35,FALSE))"
           Range("F7").Formula = "=IF(VLOOKUP($B$3,Data,36,FALSE)="""","""",VLOOKUP($B$3,Data,36,FALSE))"
           Range("E8").Formula = "=IF(VLOOKUP($B$3,Data,37,FALSE)="""","""",VLOOKUP($B$3,Data,37,FALSE))"
           Range("F8").Formula = "=IF(VLOOKUP($B$3,Data,38,FALSE)="""","""",VLOOKUP($B$3,Data,38,FALSE))"
           Range("E9").Formula = "=IF(VLOOKUP($B$3,Data,39,FALSE)="""","""",VLOOKUP($B$3,Data,39,FALSE))"
           Range("F9").Formula = "=IF(VLOOKUP($B$3,Data,40,FALSE)="""","""",VLOOKUP($B$3,Data,40,FALSE))"
           Range("E10").Formula = "=IF(VLOOKUP($B$3,Data,41,FALSE)="""","""",VLOOKUP($B$3,Data,41,FALSE))"
           Range("F10").Formula = "=IF(VLOOKUP($B$3,Data,42,FALSE)="""","""",VLOOKUP($B$3,Data,42,FALSE))"
           Range("E11").Formula = "=IF(VLOOKUP($B$3,Data,43,FALSE)="""","""",VLOOKUP($B$3,Data,43,FALSE))"
           Range("F11").Formula = "=IF(VLOOKUP($B$3,Data,44,FALSE)="""","""",VLOOKUP($B$3,Data,44,FALSE))"
           Range("E12").Formula = "=IF(VLOOKUP($B$3,Data,45,FALSE)="""","""",VLOOKUP($B$3,Data,45,FALSE))"
           Range("F12").Formula = "=IF(VLOOKUP($B$3,Data,46,FALSE)="""","""",VLOOKUP($B$3,Data,46,FALSE))"
           Range("E13").Formula = "=IF(VLOOKUP($B$3,Data,47,FALSE)="""","""",VLOOKUP($B$3,Data,47,FALSE))"
           Range("F13").Formula = "=IF(VLOOKUP($B$3,Data,48,FALSE)="""","""",VLOOKUP($B$3,Data,48,FALSE))"
           Range("E14").Formula = "=IF(VLOOKUP($B$3,Data,49,FALSE)="""","""",VLOOKUP($B$3,Data,49,FALSE))"
           Range("F14").Formula = "=IF(VLOOKUP($B$3,Data,50,FALSE)="""","""",VLOOKUP($B$3,Data,50,FALSE))"
           Range("E15").Formula = "=IF(VLOOKUP($B$3,Data,51,FALSE)="""","""",VLOOKUP($B$3,Data,51,FALSE))"
           Range("F15").Formula = "=IF(VLOOKUP($B$3,Data,52,FALSE)="""","""",VLOOKUP($B$3,Data,52,FALSE))"
           Range("E16").Formula = "=IF(VLOOKUP($B$3,Data,53,FALSE)="""","""",VLOOKUP($B$3,Data,53,FALSE))"
           Range("F16").Formula = "=IF(VLOOKUP($B$3,Data,54,FALSE)="""","""",VLOOKUP($B$3,Data,54,FALSE))"
           Range("E17").Formula = "=IF(VLOOKUP($B$3,Data,56,FALSE)="""","""",VLOOKUP($B$3,Data,56,FALSE))"
           Range("E18").Formula = "=IF(VLOOKUP($B$3,Data,58,FALSE)="""","""",VLOOKUP($B$3,Data,58,FALSE))"
           Range("E19").Formula = "=IF(VLOOKUP($B$3,Data,59,FALSE)="""","""",VLOOKUP($B$3,Data,59,FALSE))"
           Range("F19").Formula = "=IF(VLOOKUP($B$3,Data,60,FALSE)="""","""",VLOOKUP($B$3,Data,60,FALSE))"
           Range("E20").Formula = "=IF(VLOOKUP($B$3,Data,61,FALSE)="""","""",VLOOKUP($B$3,Data,61,FALSE))"
           Range("F20").Formula = "=IF(VLOOKUP($B$3,Data,62,FALSE)="""","""",VLOOKUP($B$3,Data,62,FALSE))"
           Range("E21").Formula = "=IF(VLOOKUP($B$3,Data,63,FALSE)="""","""",VLOOKUP($B$3,Data,63,FALSE))"
           Range("F21").Formula = "=IF(VLOOKUP($B$3,Data,64,FALSE)="""","""",VLOOKUP($B$3,Data,64,FALSE))"
           Range("E22").Formula = "=IF(VLOOKUP($B$3,Data,65,FALSE)="""","""",VLOOKUP($B$3,Data,65,FALSE))"
           Range("F22").Formula = "=IF(VLOOKUP($B$3,Data,66,FALSE)="""","""",VLOOKUP($B$3,Data,66,FALSE))"
           
Sheets("Amend").Select
           Range("H7").Value = "Data amended"
           MsgBox "Data added", vbOKOnly, "Amend Data"
           

'Worksheets("input").Range("H1").Value = NewRow
Worksheets("amend").Range("B3").Select
End Sub

我需要 G1 中的一个值才能使宏工作，当列表框在 G2 中放置一个索引值时，我添加了 2 以获得行号。

标签： excel

解决方案

假设组合框位于名为“amend”的工作表上，则将此代码放入该工作表的模块中。

Private Sub ComboBox1_Change()
    Me.Range("G1").Value = ComboBox1.ListIndex + 2
End Sub

请注意，您可能需要调整+2，具体取决于您填充组合框的方式。

推荐阅读