Userform help

[kieran989]

hi all,

I am trying to write a macro that will help me create a activity/hazard/control assessment for my field of work.. The idea is to have three lists. Activities are to be selected in listbox 1 and this will automatically populate the associated hazards and controls into listbox 2 and 3. Then the user will then select the appropriate hazards from the list and this will add further associate controls to the Listbox 3. Then the user will select the appropriate controls and eventually I will add something in that will create a risk assessment spreadsheet.

all of the activity-hazard, hazard-control and activity-control relationship matrices are on separate sheets.

I cant for the life of me figure out whats going on in my code. When the activities are selected it seems to be somewhat working.... however I cant all the associated hazards to list correctly

Can somebody please have a look and provide some comments?

note i have just named all the activitiy, hazard and control data and incremental name so as not to publish all the information.

Cheers,

[werafa]

Hi Kieran,

Just for fun,
can you try setting up your selection lists as named ranges and applying these dynamically to the listboxes? this seems to work well for me.
alternatively, can you set a dynamic named array and change this programatically?

If this dose not work for you, can you post the first bit of offending code here in VBAX, and talk me though it?

Thanks
Werafa

[werafa]

Public EnableEvents As Boolean

Private Sub UserForm_Initialize()
Me.EnableEvents = True
End Sub

Sub Something()
Me.EnableEvents = False
' some code that would cause an event to run
Me.EnableEvents = True
End Sub

Then, all of the controls on form should have a test if that variable as their order of business in any event code. For example,

Private Sub ListBox1_Change()
If Me.EnableEvents = False Then
Exit Sub
End If
MsgBox "List Box Change"
End Sub


http://www.cpearson.com/excel/SuppressChangeInForms.htm

[werafa]

woops this was supposed to go onto your other question

[justuptou]

I have to reading your coding, the coding in the userform1 :

Question 1)
(Dim Activiy_row << wrong spelling )

Question 2)
WorksheetFunction.VLookup(Left(UserForm1.ListBox2.List(i), 250), HC_Controls_MAtrix, j, "TRUE") = "Y" Then
( System can't be defined "Left(.... , 250)" )

Question 3)
You have 3 "for ... next" function, I suggest that you should not only use i and j for those three formula if it is not related.
specify you are using Global variable