Excel VBA question regarding running userform code against two worksheets and returning a cell value to a third worksheet

I have attached an Excel workbook with VBA code.  I am new to VBA and do not understand the relationship between worksheets in a workbook and the code written in modules, etc.  I want to call the worksheets entitled 1-Cyber... and 2-Threat... separately from the summary worksheet, run the userform code against each worksheet one at a time, and return a cell value from each sheet as indicated in the summary worksheet.  The userform code requires the user to answer a series of statements.  Please advise and thank you for your assistance.
FFIEC-Cybersecurity-Assessment-Tool---v.
mcarsonsrAsked:
Who is Participating?
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.

Patrick MatthewsCommented:
Not completely sure what you are after here, but as a starting point I would replace your code in the userform's module with the following:

Option Explicit
Option Compare Text

Dim currentrow As Long
Dim lastrow As Long
Dim firstrow As Long
Dim currentsheet As String

Const Cyber As String = "1-Cyber Risk Mgmt"
Const Threat As String = "Threat Intel & Collab"

Private Sub NextRec_Click()
Dim i As Long, cnt As Long, Ans As String

With ListBox1
       For i = 0 To .ListCount - 1
           If .Selected(i) Then
                Ans = .List(i)
               cnt = cnt + 1
           End If
       Next i
End With
If cnt = 0 Then
    MsgBox "Please Select An Answer Before Moving to the Next Statement", vbExclamation, "No Option Selected..."
    Exit Sub
End If

With ThisWorkbook.Worksheets(currentsheet)
    .Cells(currentrow, 6).Value = Ans
    lastrow = .Range("A" & Rows.Count).End(xlUp).Row
    currentrow = currentrow + 1
    ListBox1.ListIndex = -1
End With

If currentsheet = Cyber And (currentrow - 1) = lastrow Then
    currentsheet = Threat
    currentrow = 2
ElseIf currentsheet = Threat And (currentrow - 1) = lastrow Then
    MsgBox "You have answered the last question"
    Unload Me
End If

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

End Sub


Private Sub UserForm_Initialize()

currentsheet = 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


As to returning the results to the summary worksheet, why not simply do that with formulas?
0

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
Roy CoxGroup Finance ManagerCommented:
I'm not sure what you want to do with the userform, but this code shows you haow to use a Public Variable for a worksheet and change the sheet used according to the button clicked.

Option Explicit
Public wSht As Worksheet

Sub Button1_Click()
Set wSht = Worksheets("1-cyber Risk Mgmt")
MsgBox wSht.Range("K142").Value
End Sub
Sub Button2_Click()
Set wSht = Worksheets("2-Threat Intel & Collab")
MsgBox wSht.Range("K46").Value
End Sub

Open in new window


The button codes for the userfortm, etc should be in the userform code module.

Take a look at my DataBaseForm here
FFIEC-Cybersecurity-Assessment-Tool.xlsm
0
mcarsonsrAuthor Commented:
Both responses are very helpful.  

Obviously, I didn't articulate my question very well.  I would like to have separate buttons (and possibly more for more worksheets added later) on a userform to activate the other worksheets separately one at a time, then run the userform code for the activated individual worksheet and then return to the userform with the buttons that activate each worksheet, so the user can run the userforms one at a time.

Also Patrick, very good advice about returning the value of the cell with a formula, duh on my part.

Patrick, I do receive a run time error after your code takes me all the way through to the last question and I provide the OK response to the message.  The error shows at the with statement on line 43.  please advise.

Roy, thank you for your code on the buttons.

I think both of you deserve solution credit, which I will record after your follow-up.  Assuming this procedure is appropriate as I am new to Experts Exchange??

Thank you both again
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Roy CoxGroup Finance ManagerCommented:
You do not need to activate the worksheets.

Simply try adding buttons to a UserForm then using the code that I suggested
FFIEC-Cybersecurity-Assessment-Tool.xlsm
0
Patrick MatthewsCommented:
Patrick, I do receive a run time error after your code takes me all the way through to the last question and I provide the OK response to the message.  The error shows at the with statement on line 43.  please advise.

OK, so the Unload Me is causing variables to lose state.  Immediately after that "Unload Me" line, add a new line with:

Exit Sub

Open in new window


I think both of you deserve solution credit, which I will record after your follow-up.  Assuming this procedure is appropriate as I am new to Experts Exchange??

When you feel more than one Expert helped you to a good solution, a split is a very acceptable outcome :)
0
Roy CoxGroup Finance ManagerCommented:
Did the code work for you on the userform that I added?

You will find the DataBaseForm helpful, there are around 50 a week downloaded.
0
mcarsonsrAuthor Commented:
Roy,

I am not sure what you mean by the following statement??  Thank you

You will find the DataBaseForm helpful, there are around 50 a week downloaded.
0
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.