asked on

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.

Roy Cox

Are the userforms to be opened UserForm 1 then UserFortm2?

UserFirm 2 has the RowSource set manually, this will need removing.

Roy Cox

Edit

forget my last post, I see what you mean. I'll post basck soon

mcarsonsr

ASKER

Thank you very much!

Roy Cox

What you want will be easily doable, but what exactly goes into UserForm1. You have a whole table of data on each sheet.

Martin Liss

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

mcarsonsr

ASKER

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?

mcarsonsr

ASKER

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 Liss

@mcarsonsr: In my downloaded copy of your workbook, Userform1 is corrupted and can't be opened.

Martin Liss

What error do you get?

Roy Cox

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.

mcarsonsr

ASKER

Roy,

Thank you. I do not see the attached example.

Roy Cox

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.

mcarsonsr

ASKER

Thank you very much for your assistance!

Martin Liss

mcarsonsr, can you tell me what error you get?

mcarsonsr

ASKER

Martin,

I receive the run-time error '9':

Subscript out of range

on the line Sheets(Lbdomains.Text).Activate

Thank you

Martin Liss

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 Cox

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

mcarsonsr

ASKER

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.

mcarsonsr

ASKER

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 Liss

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

mcarsonsr

ASKER

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 Cox

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.

mcarsonsr

ASKER

Thank you. will do

ASKER CERTIFIED SOLUTION

Roy Cox

membership

This solution is only available to members.

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

Start Free Trial

mcarsonsr

ASKER

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.

mcarsonsr

ASKER

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 Cox

No problem

I had sorted those problems out in the latest upload. The first one was a rush job