[Solved] InputBox Entry to worksheet range cell.

[SherriM]

I have a workbook with two spreadsheets. Sheet1 includes two columns as below. Column F is a drop down list based on named range on sheet 3 and Column G is xlookup formula as below.


=XLOOKUP([@CUSTOMER],CustomerTable1[Customer], CustRange,"")


When a customer already on the drop down list is chosen, the CustNum column populates with appropriate CustNum from the table below. However, when a new customer is added on sheet1, there is VBA code to add the new customer name to the bottom of the Customer column on sheet 3. Then an inputbox allows the new customer number to be entered as coded below.




Private Sub Worksheet_Change(ByVal Target As Range)

Dim CustNumb As Variant
Dim rngNum As Range

CustNumb = Application.InputBox(Prompt:="Enter a Number ONLY", Title:="Enter Number for New Customer", Type:=1)

If CustNumb <> "" Then
rngNum("CustRange").End(xlUp).Offset(1, 0).Select
rngNum.Value = CustNumb

End If
End Sub


The new customer number should be showing up in the last row of sheet 3, column E so the xlookup formula can have it enter on sheet1, Column G, but it does not.

Any help is much appreciated.

 

[Aniruddah Alam]

Dear Sherrim,
Can you share your file with us as we are having difficulties understanding the context clearly?
In your post, you said you used a drop-down list (i.e. Data Validation) on the Customer column of Sheet1 and used a Named Range on Sheet3 as the Source List (I assume it is the Customer column on Sheet3).

However, after applying the Data Validation, you'll only be able to enter customer names that are on the source list. If you try to put anything that is not in the source list, you will get an error message like this.

To provide you with a complete solution, please share your file with us so we can take a closer look. Thank you.

Aniruddah
Team Exceldemy

 

[SherriM]

Dear Aniruddah,

Thank you for your assistance. I've attached a file with sample data so you can see how it is working -- and not working.

 

[Aniruddah Alam]

Dear Aniruddah,

Thank you for your assistance. I've attached a file with sample data so you can see how it is working -- and not working.

Dear SherriM,
Thanks for sharing your file with us! I was able to figure out what was causing the issue and fix it for you. Previously, the code on Sheet3 was not properly determining the last cell of the CustRange. I have changed the code so that it can now properly enter the value from the InputBox to the last cell of the CustRange. I have also added a few lines of code so that the program only runs when you're working with the cells inside the CustomerList. Hope that makes things easier for you! I am attaching the corrected Excel File. if you have any further queries, feel free to ask.
Regards
Aniruddah
Team Exceldemy

 

[SherriM]

Wow, Aniruddah, it's working! Thank you so much!

 

[Aniruddah Alam]

Wow, Aniruddah, it's working! Thank you so much!

Glad to hear that! It was my pleasure to help you.

Aniruddah
Team Exceldemy