Excel VBA- Viewing and Updating existing worksheet data with Excel UserForm with Dependent Comboboxes

[charliechaz]

Hello,

I’m new to excel VBA and I need some help with an excel workbook that houses requirements risk assessments. The assessments are completed but need to be analyzed overtime. The outcome of the analysis may prompt me to:

• Edit existing requirements and assessments
• Add new requirements and assessments
• Deleted existing requirements

I’d like to perform these tasks via VBA as the ‘Form’ function in excel does not allow me to expand fields to display the entire content of a cell and more importantly, does not allow me to edit any existing values.

My Workbook has the following worksheets: ‘New_RRA’, ‘Data’ (used for calculated assessment cells) and a ‘Topics’ worksheet that was added to contain unique ‘Topic’ values.

My ‘New_RRA’ worksheet includes the following columns:

• Topic (Composed of 19 topics. Playing around, I added these in a separate worksheet (‘Topics’) but it may not be needed as I figured out how to extract unique values in my attempts of getting this to work in VBA. More on this later.)
• URS_ID
• URS_Description
• Hazard
• Hazard_Effects
• Potential_Causes
• Severity
• Probability
• Prevention Controls
• Risk_Class
• Detectability
• Detection_Controls
• Risk_Priority
• Mitigation

UserForm Requirements:

• Record View
  
  1. I would like to have a userform that would allow me to select the ‘Topic’ from a dropdown.
  2. Based on the topic selected, I want to see a listing of associated ‘URS_ID’s along with associated ‘URS_Description’s from a dropdown list. This will allow for the accurate selection of a requirement that needs to be analyzed. (Perhaps a scroll function would be adequate here)
  3. Based on the ‘URS_ID’ selected, I want the form to populate the ‘URS_Description’ field.
  4. After the ‘URS_Description’ is obtained, all the remaining record column cells (D:N) shall be displayed in their respective fields as well.
• Functionality
  
  1. After a record is displayed on the UserForm, I’d like to have the ability to edit any field and update the ‘New_RRA’ worksheet data with the updated information.
  2. A nice-to-have would be to include when (Time stamp) the update occurred and by whom (ApplicationUser).

What I’ve done so far
I have searched for a while and made little progress. I watched several videos that came close to what I needed but got stuck.

I started by building a UserForm with ‘Topic’, ‘URS_ID’ and ‘URS_Description’ comboboxes.

As I stated before, I got the ‘Topic’ combobox (combobox1) to work but can make ‘URS_ID’ (combobox2) and URS_Description (combobox3) to work.

Here’s the code so far:

Private Sub UserForm_Initialize()

Dim sh As Worksheet

Set sh = ThisWorkbook.Sheets("New_RRA")

Dim v, e

With Sheets("New_RRA").Range("A2:A107")

v = .Value

End With

With CreateObject("scripting.dictionary")

.comparemode = 1

For Each e In v

If Not .exists(e) Then .Add e, Nothing

Next

If .Count Then Me.ComboBox1.List = Application.Transpose(.keys)

End With

End Sub

You’ll note that I tried to incorporate a VLOOKUP function for combobox1, but this only displays one (I believe the first instance) ‘URS_ID’.


Any help is appreciated.

Please see the attached workbook (Apologies, I could not figure out how to attach my excel file).

 

[Akuini]

Please see the attached workbook (Apologies, I could not figure out how to attach my excel file).

You cannot attach a file on this forum. However, you can upload a sample workbook (without sensitive data) to a file-sharing site like Dropbox.com or Google Drive, and then share the link here.

 

[charliechaz]

As I stated before, I got the ‘Topic’ combobox (combobox1) to work but can make ‘URS_ID’ (combobox2) and URS_Description (combobox3) to work.

Correction: As I stated before, I got the ‘Topic’ combobox (combobox1) to work but can't make ‘URS_ID’ (combobox2) and URS_Description (combobox3) to work.