VBA Code to Load a Userform, have the user make a choice, use the choice to activate a specific worksheet, and have another userform load the data from the activated worksheet

In the attached workbook I have two userforms.  I would like for userform2 to allow the user to pick a response from the listbox, then when the user hits the enter button the worksheet title from the user response should activate the specific worksheet with that name, and then userform1 should be shown and the records from the activated worksheet should be used to populate the userform1.  I have commented out the code in userform2, because the errors stop the process and the code in userform1 will not work.  Also, a previous question was answered for me with the code in userform1, which currently loops through the specific worksheets.  I now want to the user to choose the worksheets as described above rather than loop through them.

Thank you very much for your assistance!
FFIEC-Cybersecurity-Assessment-Tool---v.
mcarsonsrAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Roy CoxGroup Finance ManagerCommented:
Are the userforms to be opened  UserForm 1 then UserFortm2?

UserFirm 2 has the RowSource set manually, this will need removing.
Roy CoxGroup Finance ManagerCommented:
Edit

forget my last post, I see what you mean. I'll post basck soon
mcarsonsrAuthor Commented:
Thank you very much!
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Roy CoxGroup Finance ManagerCommented:
What you want will be easily doable, but what exactly goes into UserForm1. You have a whole table of data on each sheet.
Martin LissOlder than dirtCommented:
Change CBChoosedo_Click  to the following. Note that the names in the listbox must be exactly the same as the names of the sheets.

Private Sub CBChoosedo_Click()
Sheets(Lbdomains.Text).Activate
UserForm1.Show
Me.Hide
End Sub

Open in new window


And in Userform1 make this change.

Private Sub UserForm_Initialize()

currentsheet = ActiveSheet 'Cyber

currentrow = 2
ListBox1.ListIndex = -1
With ThisWorkbook.Worksheets(currentsheet)
    Dom.Text = .Cells(currentrow, 1).Text
    AssessText.Text = .Cells(currentrow, 2).Text
    Subcat.Text = .Cells(currentrow, 3).Text
    Maturity.Text = .Cells(currentrow, 4).Text
    Decstate.Text = .Cells(currentrow, 5).Text
    .Cells(currentrow, 6) = ListBox1.Text
End With
End Sub

Open in new window

mcarsonsrAuthor Commented:
When userform1 is loaded it should populate with the first record in the data table worksheet.  The user then responds to the declarative statement by choosing a response from the listbox.  The record is then passed back to the data table with the response from the listbox added.  Then the user performs the same thing by choosing the next record button until the user reaches the end of the data table.  When the last row is written back to the table I would like the userform code to end and send the user back to the userform2, where I need to do more coding.

Does that help?
mcarsonsrAuthor Commented:
Martin,

Thank you for your response.  The change you made to the CBChoosedo_Click produces the same run time error that I have been stuck on.  Thoughts?
Martin LissOlder than dirtCommented:
@mcarsonsr: In my downloaded copy of your workbook, Userform1 is corrupted and can't be opened.
Martin LissOlder than dirtCommented:
What error do you get?
Roy CoxGroup Finance ManagerCommented:
Choosing a different sheet as a source for the Listbox is simple, although I would have only one UserForm.

Have a look at this example that has next and previous buttons. It can be adapted for what you want, let me know and I'll help you change it.
mcarsonsrAuthor Commented:
Roy,

Thank you.  I do not see the attached example.
Roy CoxGroup Finance ManagerCommented:
Sorry, I'll try again

Update: it's a zip file and will not upload for some reason. I'll add a new userform to your file with some ideas in it. It might not be finished tonight but I'll certainly upload it tomorrow morning.
mcarsonsrAuthor Commented:
Thank you very much for your assistance!
Martin LissOlder than dirtCommented:
mcarsonsr, can you tell me what error you get?
mcarsonsrAuthor Commented:
Martin,

I receive the run-time error '9':

Subscript out of range

on the line Sheets(Lbdomains.Text).Activate

Thank you
Martin LissOlder than dirtCommented:
That's because the names of your sheets are different from the names in the listbox. So for example change the "1-Cyber Risk Management & Oversight" value in your Domains named range to "1-Cyber Risk Mgmt"
Roy CoxGroup Finance ManagerCommented:
Hve a look at UserForm 3 and see if it's on the right lines. If it is I'll help you finish it tomorrow
FFIEC-Cybersecurity-Assessment-Tool.xlsm
mcarsonsrAuthor Commented:
You are correct.  I have become very confused.  I have changed the names of the worksheets to match and your code is activating the correct worksheet, but userform1 is still pulling from the first worksheet regardless of the choice from the listbox.  I know that is a coding problem in userform1 that i cannot figure out.  I have attached another copy of the file.

Thank you
FFIEC-Cybersecurity-Assessment-Tool---v.
mcarsonsrAuthor Commented:
Roy, that is right on the mark and a vast improvement over my coding (I am quite the novice).  I especially like the way you combined the two forms.  I would like the user to be able to move forward and backwards through the form to make any changes they desire.  Then as the next steps I am going to report on the responses to the various data tables, but that is a struggle for after I get past this point.

Thank you very much
Martin LissOlder than dirtCommented:
You may well want to use Roy's solution but if you make the change in line 3 that I posted above, it should load the right data.

Private Sub UserForm_Initialize()

currentsheet = ActiveSheet 'Cyber

currentrow = 2
ListBox1.ListIndex = -1
With ThisWorkbook.Worksheets(currentsheet)
    Dom.Text = .Cells(currentrow, 1).Text
    AssessText.Text = .Cells(currentrow, 2).Text
    Subcat.Text = .Cells(currentrow, 3).Text
    Maturity.Text = .Cells(currentrow, 4).Text
    Decstate.Text = .Cells(currentrow, 5).Text
    .Cells(currentrow, 6) = ListBox1.Text
End With
End Sub

Open in new window

mcarsonsrAuthor Commented:
Roy,

I did notice the forms are not populating with the correct column data in each row, but that should be easy to fix I would think.
Roy CoxGroup Finance ManagerCommented:
I'm going to have my dinner now. Let me know what we need to change and I'll have a look tomorrow afternoon. basically I think you just need a button to save the data back to the table which won't be difficult.

I'll add a button and some notes to the code in the morning and you can check it.
mcarsonsrAuthor Commented:
Thank you. will do
Roy CoxGroup Finance ManagerCommented:
Hi

Here's a sample userform that works how I think that you want it.

There are buttons for first , next, last and previous records.
A button to amend a record
A button to enter a new record

let me know if this is what you want
FFIEC-Cybersecurity-Assessment-Tool.xlsm

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mcarsonsrAuthor Commented:
Roy,

Thank you very much.  This is a great start to me moving forward with the coding.  The previous button doesn't seem to populate each field correctly when clicking back through the records using the previous button and I receive an error when it reaches the first record, but I will work on those elements as well as including my response listbox in the code you have provided.
mcarsonsrAuthor Commented:
Roy,

Please ignore my last post.  I pulled up the version I was working on, not your latest.  I will test your version now, but it looks like it has everything I want.  Thank you again!
Roy CoxGroup Finance ManagerCommented:
No problem

I had sorted those problems out in the latest upload. The first one was a rush job
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.