0

I created a data entry form which consists of a dropdown list with the country name.

However, when I use data entry code to add to another worksheet, the input only shows the sequence of the list (not the text in the list). How can I change these sequences to the text in the list?

In addition, I already named the column in master data worksheet. How can I add the information to the master data worksheet by referring to column name instead of the number of column?

Here is my vba code

'Add information in data form
   Application.ScreenUpdating = False
   
   Dim NextRow As Long, Lastrow As Long
   
   Lastrow = Sheets("CustomerMaster").Range("C" & Rows.Count).End(xlUp).Row
   
   NextRow = Lastrow + 1
   
   'If formValidation = True Then


        Sheets("CustomerMaster").Cells(NextRow, 1) = Sheets("Customer Data Entry").TextID
        Sheets("CustomerMaster").Cells(NextRow, 2) = Sheets("Customer Data Entry").TextCompany
        Sheets("CustomerMaster").Cells(NextRow, 3) = Sheets("Customer Data Entry").DropDowns("Drop Down 8")
        Sheets("CustomerMaster").Cells(NextRow, 4) = Sheets("Customer Data Entry").TextRevenue
        Sheets("CustomerMaster").Cells(NextRow, 5) = Sheets("Customer Data Entry").TextAddress
        Sheets("CustomerMaster").Cells(NextRow, 6) = Sheets("Customer Data Entry").DropDowns("Drop Down 11")
        Sheets("CustomerMaster").Cells(NextRow, 7) = Sheets("Customer Data Entry").TextInitialCust
        Sheets("CustomerMaster").Cells(NextRow, 8) = Sheets("Customer Data Entry").TextSource
        Sheets("CustomerMaster").Cells(NextRow, 9) = Sheets("Customer Data Entry").TextEntered
        Sheets("CustomerMaster").Cells(NextRow, 10) = Sheets("Customer Data Entry").DropDowns("Drop Down 21")
        Sheets("CustomerMaster").Cells(NextRow, 11) = Sheets("Customer Data Entry").TextRemarkCust
Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Nalin Pongmanavuth
  • 11
  • 2
  • 1
    have not understood your requirement. Can you exlain, perhaps with screenshots of what you exactly want? – Siddharth Rout Dec 14 '20 at 06:17
  • For example; In my Data Entry form, there is a dropdown box of the country e.g. USA, UK, Canada, Mexico, respectively. When I select USA in the Data Entry form and click add button with the above VBA. Instead of input "USA" in "CustomerMaster" worksheet, it inserts "1" (It's the rank number of USA in the list). If I select "Canad"a, It will input "3" in "CustomerMaster" worksheet instead of "Canada". I would like it to input the country ("USA" or "Canada"). How can I change this? – Nalin Pongmanavuth Dec 14 '20 at 08:36
  • Yes because you are using `.DropDowns("Drop Down 8")` which will give you the listindex and not the value. What you want is explained [HERE](https://stackoverflow.com/questions/9578038/return-the-selected-text-from-a-dropdown-box) by @chrisneilsen – Siddharth Rout Dec 14 '20 at 08:45

0 Answers0