First time VBA user, PLEASE HELP

[Godfrey2000]

Hi revered Excel experts,

Quick version of the request: Userform textbox > text is sent to a table on another sheet under the header that matches the value selected from a dropdown list on the excel sheet > text is also sent to another table as a new header

I'm really happy to be on this forum and I'd like to be the one who saves the day for someone, but today I need help.

I've created an exciting new project at work to allow client service agents to record and track client issues so we can make improvements.

I've got a series of cascading dependent dropdown menus in Excel (which I'm very proud of), now I need a user form to enter text to a table on another sheet. I've been reading a VBA book and searched multiple forums, but only skimmed the surface.

Here's what I'm trying to create: ​I've got enough experience to create the Userform in VBA and the residual coding to make it functional. Just need the possible coding to do the following:
1) the user makes a selection from an excel dropdown menu on the sheet itself, then clicks a button below it ("enter new issue") to pull up the user form
2) when text is entered in the textbox on the user form, the user clicks Save and the text is sent to a table on another sheet (ListObjects("Issues")). The text is written in the next available row under the header that matches the selection in the dropdown from step 1 (using FIND or MATCH?)
3) at the same time, the text entered in the textbox is added as a new header of a table (ListObect("Cases")) on another sheet

I'm using both Excel 2006 and Excel for Mac 2011 - so code for either version is fine.

If anyone can point me in the right direction I would be TREMENDOUSLY GRATEFUL.

Santa's watching,

David T

 

[John_w]

See if this works for you. The dropdown box is a Form Control Combo Box named "Drop Down 1" (its default name) placed on a sheet I have named "Control".

The Issues table is on the Issues sheet, and the Cases table is on the Cases sheet.

The Save button on the userform is named cmdSave and the following code is run when the button is clicked (put the code in the userform module):

Private Sub cmdSave_Click()
    
    Dim dropdownValue As Variant
    Dim issuesTable As ListObject, casesTable As ListObject
    Dim issuesColumn As Variant
    Dim nextRow As Long
    Dim newCasesColumn As ListColumn
    
    'Get the value in the "Drop Down 1" Form Control Combo Box on the "Control" sheet
    
    With ThisWorkbook.Worksheets("Control").Shapes("Drop Down 1").ControlFormat
        dropdownValue = .List(.ListIndex)
    End With
    
    'Get the "Issues" table on the "Issues" sheet
    
    Set issuesTable = ThisWorkbook.Worksheets("Issues").ListObjects("Issues")
    
    'Look for drop down box value in header row and if found put txtIssue value in next empty row of the found column
    
    issuesColumn = Application.Match(dropdownValue, issuesTable.HeaderRowRange, 0)
    If Not IsError(issuesColumn) Then
        nextRow = issuesTable.ListColumns(issuesColumn).Range.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
        issuesTable.Range(nextRow, issuesColumn).Value = Me.txtIssue
    Else
        MsgBox "Dropdown value '" & dropdownValue & "' not found in header row of Issues table"
    End If
    
    'Put txtIssue value as new column header in "Cases" table on "Cases" sheet
    
    Set casesTable = ThisWorkbook.Worksheets("Cases").ListObjects("Cases")
    Set newCasesColumn = casesTable.ListColumns.Add
    newCasesColumn.Name = Me.txtIssue

End Sub

 

[Godfrey2000]

IT WORKS!!!!! John W, you are my saviour! I'm giving you a big bearhug brother (there's no hug emoticon, but close your eyes and feel my sincerest embrace).

I must have spent 3 whole days of my life trying to figure it out on my own. I was micro-quitting along the way while having to face our clients, the department, and the whole company who's counting on me. Now I can declare a triumph.

When this project takes flight it will give client facing agents REAL POWER to cause change at the highest level. Now a client service agent can courageously hand deliver a report to the CEO that says, "Here's the issue, # of clients who call about it, and how much money they lose because of it. And here's our mission statement: Help people become financially secure." BADABANG!! The data will do the rest of the talking.

You've made a bigger difference than you know.

And now I'm inspired to continue learning more VBA so I can cause someone else the fulfillment you left me.

Warm regards,

David T

 

[Godfrey2000]

Hi John,

Just a quick question when you have a moment. I'd like to get the value that was selected in a data validation dropdown list on the worksheet, instead of the Form Control Combo Box. I realize I didn't specify when I posted the question. I tried to search for the code online but spent 2 hours with no luck. I imagine it would be similar coding for 'Get the value in the "Range.("A1")" drop down list on the "Control" sheet'?

Many thanks



'Get the value in the "Drop Down 1" Form Control Combo Box on the "Control" sheet

With ThisWorkbook.Worksheets("Control").Shapes("Drop Down 1").ControlFormat
dropdownValue = .List(.ListIndex)
End With

 

[John_w]

Just replace those lines with:

    'Get the value in the Data Validation List, cell F2 on the "Control" sheet
    
    dropdownValue = ThisWorkbook.Worksheets("Control").Range("F2").Value

Change the sheet name and cell of the data validation dropdown as required.

 

[Godfrey2000]

Nice!! It works perfectly. With VBA I thought, "The most complex answer is often the correct one."
John, you've proved me wrong.

Thank you kindly,

David T