asked on

Excel User Form VBA Help

To extend onto this question Dynamic Excel Input Form , I want to be able to keep the Region text next to Regions. Maybe instead of multiple dropdowns, it can just populate the cells next to it. I just need the regions going across above the table. Also, what if there was another table underneath that also had to populate and be consistent with the above. See attached.

Thank you!
29000692b--2-.xlsm

Martin Liss

Just out of curiosity, why do you need the regions going across the top?

kwarden13

ASKER

As part of the overall form, so I didnt upload the whole thing, but here it is...
Example-1.xlsx

Martin Liss

I still don't really understand but I don't have any problem doing it. Please see the attached.
29000921.xlsm

kwarden13

ASKER

You are quick! Trust me I rather have built this as a web form. Thank you!!! I am going to test and see if this works for different scenarios.

I will update shortly and close question if all is good.

kwarden13

ASKER

One issue I found is if I add 3 Regions and delete the middle region, the wrong one is getting deleted. For example, add mid-west, northeast, northwest, delete northeast, but mid-west column gets deleted.

Martin Liss

Oops I didn't notice that.

Martin Liss

Corrected.
29000921a.xlsm

kwarden13

ASKER

Hi Martin-

I tried to change the vba since my fields are setup in different cells. However, I still am not getting it right. Can you help? See attached. Do you want me to open another question and close this one?

Kelly
29000921a-v2.xlsm

Martin Liss

Hopefully I can change it for you; I'll get back to you.

ASKER CERTIFIED SOLUTION

Martin Liss

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

kwarden13

ASKER

Is there something special I need to do to make the code run? When I copy the code to another workbook (with the same fields, names, and cells), nothing happens when selecting from the drop down.

Kelly

Martin Liss

Well one thing to try is to type Application.EnableEvents = True in the Immediate window and press return, and then see if the code runs.

kwarden13

ASKER

In the vba screen? Am i suppose to run macro 'x'?

Martin Liss

When you are in the Visual Basic area (where you maintain your code), type ctrl+g. That will bring up the Immediate Window. Type Application.EnableEvents = True in that window and press return, and then see if the code runs.

kwarden13

ASKER

I figured out this does not run. In your workbook the macro shows sheet1.x, and in my mine it just shows x....

Sub x()
MsgBox WorksheetFunction.Max(ListObjects("SpendingTotals").ListRows(ListObjects("SpendingTotals").ListRows.Count).Range.Row)
End Sub

Open in new window

Martin Liss

Delete that sub. It's not needed.

kwarden13

ASKER

Got it to work :) Now just testing. Thank you

I am going to open a new questions if you want to answer that too.

Martin Liss

You're welcome and I'm glad I was able to help.

If you expand the “Full Biography” section of my profile you'll find links to some articles I've written that may interest you.

Marty - Microsoft MVP 2009 to 2016
Experts Exchange MVE 2015 and 2016
Experts Exchange Top Expert Visual Basic Classic 2012 to 2016