Home > Software engineering >  How can I show a row number in a spreadsheet cell for the selection from an activex combo box?
How can I show a row number in a spreadsheet cell for the selection from an activex combo box?

Time：10-14

I have a worksheet that is used for updating existing entries in a table and use a dropdown to select the entry to update and a macro to write the entries back to the data table. Initially I used a list box and got the value I want as the 'Cell link', however I needed to change that to an ActiveX combo box so that the list showed two columns in the drop down list, Surname and Christian name, to enable individuals with the same surname but different Christian names to be selected. However by using a combo box I lost the row number that the macro needs. I need an Excel spreadsheet cell with the row number of the selected value in so that my macro knows where to enter the updated entry. I have tried =MATCH(B3,Data!A3:A99,0) but that only shows the first occurrence of a surname. I cannot find a property for the combo box that I can alter and have searched and searched the internet but haven't found anything I understand. Thank you

CodePudding user response：

There is no code to see

Private Sub ComboBox1_Change()

End Sub.

The macro code is:

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

I need a value in G1 for the macro to work, which it did when the list box put an index value in G2 to which I added 2 to get the row number.

CodePudding user response：

Assuming the combobox is on the sheet named 'amend', put this code in the module of that sheet.

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

Note, you might need to adjust 2, depending on how you are populating the combobox.

Page link：https//www.codepudding.com/Softwareengineering/152133.html

Prev:Add numbers to rows by block with Excel VBA
Next:Add .xll as AddIn to Excel
  • Related

About Us:  Contact Us      Terms of Service       Privacy Policy

Copyright © 2010-2023，Powered By CodePudding