User input data setup box

[dsrt16]

I want to create a setup form where users can create items that will go into a named range used in data validation lists.

I know how to create a user input form to get it to populate into one certain cell, but not in an undetermined range, like I need here.

I have already created all the named dynamic lists. Now the user will just populate it by filling out a form.

Example: One of the dynamic named lists is called material options. It starts in cell P9 and can go up to cell P100. I used offset and counta to create the named list since I don't know how many entries the user will have.

Now on the setup page, I want to have a user input form asking them to list all the different kinds of materials they edit. Then this will populate into the dynamic material list.

So a user might enter into the form websites, novels, nonfiction books, resumes, dissertations. And then cell P9:P13 will auto populate with those answers.

A different user might have only 3 materials they edit, so then they would only auto populate into cell P9:P11.

 

[ParamRay]

Here's how I did it:

1) Create a userform with a textbox and a commandbutton.
2) On the textbox, set the MultiLine property to True.
3) On the textbox, set the EnterKeyBehavior property to True.
4) Add some code behind the userform, similar to this:

Private Sub CommandButton1_Click()
  Dim Address As String
  Dim Items() As String
  Dim Count As Integer
  
  On Error GoTo ErrHandler
  Items = Split(Me.TextBox1.Value, vbCrLf)
  Count = UBound(Items) - LBound(Items) + 1
  
  With ThisWorkbook.Sheets("Sheet1") ' <-- Sheet containing the dynamic range
    Address = .Range("MaterialOptions").Address
    .Range("MaterialOptions").ClearContents
    .Range(Address).Resize(Count, 1).Value = Application.Transpose(Items)
  End With
  
  Unload Me
  Exit Sub
  
ErrHandler:
  MsgBox Err.Description, vbExclamation
  With Me.TextBox1
    .SetFocus
    .SelStart = 0
    .SelLength = .TextLength
  End With
End Sub

The user then enters items into the textbox, each item on a separate line. Once they click the commandbutton, the items will get populated into the dynamic named range.

 

[dsrt16]

I tried this. I got a subscript out of range error message.

Maybe the problem is in how I defined the MaterialOptions in name manager. =OFFSET('List Projects'!$P$9,0,0, COUNTA('List Projects'!$P$9:$P$100))

This has worked for manual entry as whatever I put in cell P9 and onward is part of that name and goes into the data validation list.

But when trying the userform entry method, I got that error. Any ideas?

 

[ParamRay]

Hello, did you swap "Sheet1" in the code with "List Projects"? Did you check your named range is exactly called "MaterialOptions"?

 

[dsrt16]

I did swap the Sheet1 with List Projects; however, I accidentally put it as ListProjects without the space. I fixed it and put in the space. I no longer get the subscript out of range error, but now I get this error: application defined or object defined error.

Here is the code.

Private Sub MaterialSubmit_Click()
  Dim Address As String
  Dim Items() As String
  Dim Count As Integer
  
  On Error GoTo ErrHandler
  Items = Split(Me.TextBox1.Value, vbCrLf)
  Count = UBound(Items) - LBound(Items) + 1
  
  With ThisWorkbook.Sheets("List Projects")
    Address = .Range("MaterialOptions").Address
    .Range("MaterialOptions").ClearContents
    .Range(Address).Resize(Count, 1).Value = Application.Transpose(Items)
  End With
  
  Unload Me
  Exit Sub
  
ErrHandler:
  MsgBox Err.Description, vbExclamation
  With Me.TextBox1
    .SetFocus
    .SelStart = 0
    .SelLength = .TextLength
  End With
End Sub

Thank you for your help.

 

[ParamRay]

Check the name of your textbox. It is "TextBox1" in the code. Change the code to match your textbox.

 

[dsrt16]

That is the name of the textbox.

Here is a screenshot of some of the code, the userform, and the name of the textbox:

http://beaconpointservices.org/wp-content/uploads/2018/03/1.png


Here is a screenshot of the named dynamic range. Some things have been moved around on my spreadsheet, so it now starts in cell N10 with the material options heading in cell N9.

http://beaconpointservices.org/wp-content/uploads/2018/03/2.png

And here is the setup page where users click on enter material to open up the form. The form opens, but after clicking submit I get that error message.

http://beaconpointservices.org/wp-content/uploads/2018/03/3.png

 

[ParamRay]

Please do a test: comment out the On Error line. Then post a screen shot of which line gives the error...

 

[dsrt16]

Address = .Range("MaterialOptions").Address

This line is giving the error.

My dynamic range is called MaterialOptions. And it refers to: =OFFSET('List Projects'!$N$10,0,0,COUNTA('List Projects'!$N$10:$N$99))

http://beaconpointservices.org/wp-content/uploads/2018/03/4.png

http://beaconpointservices.org/wp-content/uploads/2018/03/5.png

 

[dsrt16]

I went into the named range, and found that the scope was set to Workbook. Not the specific sheet "List Projects."

So I changed the code from

With ThisWorkbook.Sheets("List Projects")

To

With ThisWorkbook

But now I got a different error: Compile error. Method or data member not found. The debugger highlights the first line:

Private Sub MaterialSubmit_Click()

But that is the name of the button.

Then I tried changing the code to

With ThisWorkbook.Sheets("List Projects")
    Address = ActiveWorkboook.Range("MaterialOptions").Address

That gave object required error and highlighted the address line.

I have checked and checked. The named range is MaterialOptions. Perhaps, I have the refers to formula wrong.

Is this dynamic range formula correct? =OFFSET('List Projects'!$N$10,0,0,COUNTA('List Projects'!$N$10:$N$99)) --Thus referring to N10:N99 as needed.