Excel VBA - Add value from input box to list in column

Question

I'm currently making an expense form but I'm struggling for a list of expense categories, I want to make it so that I can type the category I choose in an input box, that then goes in a list which is used as a data validation. I want the values to add into column A on Sheet2

I've tried the below so far

Sub Button1_Click()
    Dim ExpenseName As String

    ExpenseName = InputBox( _
      "Type in the name of the category you want to add", _
      "Add Expense Category", _
      "Type expense category here")

    If Len(ExpenseName) = 0 Then
        MsgBox "No category chosen"
        Exit Sub
    End If

    '**Struggling what to put here**
End Sub

I have added the below, please see.

Sub Button1_Click()
Dim ExpenseName As String
ExpenseName = InputBox( _
"Type in the name of the category you want to add", _
"Add Expense Category", _
"Type expense category here")
If Len(ExpenseName) = 0 Then
MsgBox "No category chosen"
Exit Sub
End If
Dim strList As String
Dim ws As Worksheet
Dim eRow As Long

eRow = Sheet2.Cells(Sheet2.Rows.Count, 1).End(xlUp).Row

With ThisWorkbook.Sheets("Sheet1").Range("D4:D21").Validation
    .Delete      'Delete previous validation
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
        Formula1:="=Sheet2!$A$1:$A$" & eRow
End With
End Sub

Jordan · Accepted Answer · 2017-05-08 13:30:33Z

2

Provided there is already validation in the range, you could do something like this (not tested):

EDITED:

Sub Button1_Click()

Dim ExpenseName As String
Dim eRow as Long

ExpenseName = InputBox( _
    "Type in the name of the category you want to add", _
    "Add Expense Category", _
    "Type expense category here")

If Len(ExpenseName) = 0 Then
    MsgBox "No category chosen"
    Exit Sub
End If

With ThisWorkbook.Sheets("Sheet2")
    eRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
    .Cells(eRow, "A").Value = ExpenseName
End With

With ThisWorkbook.Sheets("Sheet1").Range("D4:D21").Validation
    .Delete      'Delete previous validation
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
        Formula1:= "=Sheet2!$A$1:$A$" & eRow
End With

End Sub

edited May 8, 2017 at 13:30

answered May 8, 2017 at 12:25

Jordan

4,5342 gold badges22 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Jack Brownridge Over a year ago

I've tried this and it shows 'Run-time error '1004': Application-defined or object-defined error @Jordan

Jack Brownridge Over a year ago

strList = .Formula1

Jordan Over a year ago

Okay, it could be because the range which the validation applies to is different, I've updated the answer now. What is the range that the data validation applies to?

Jack Brownridge Over a year ago

Data validation is on cells D4:D21 and pulls the list of data from Sheet2 A1:A5 but obviously that list of data is going to expand

Jordan Over a year ago

Oh I see, I was doing the formula based on you having a string as the formula as opposed to a range of cells. Do you want the range of cells to simply be updated? - I'll update the answer now because the data validation is on the wrong cells

|

Collectives™ on Stack Overflow

Excel VBA - Add value from input box to list in column

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related