VBA Forms Module Help please.

[lee2boy]

Gents is there anybody that could take a look and see if they can help.
On tab 1 of the workbook if you click on the the cell (MATERIALS) a form with a drop down box will appear which works fine.
I'm attempting to add a simialr form for the cell (LABOUR) but I really struggling to complete and would aslo like to add another form for Plant etc.
Is there anybody that can solve the problem i would grateful
Thanks for any help gents
Regards
Lee

[CodeNinja]

lee2boy,
A couple of things I noticed... First I could not find a cell with the value "MATERIALS" in it. There is a "Materials Each Item" cell, and cells with values under that, but unless I am missing something I did not see a cell with the value "MATERIALS". I created one, and the code worked fine.

Assuming you have a cell MATERIALS and LABOR and Plant etc... you could easily modify your code to include a select case statement... that would look like this:

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
Cancel = True
Select Case Target
Case Is = "MATERIALS"
Shows
Case Is = "LABOUR"
shows2
Case Is = "PLANT"
shows3
End Select
End Sub

Of course, you would have to add additional subroutines shows2 and shows3 in your module:

Sub Shows()
UserForm1.Show
End Sub


Sub shows2()
UserForm2.Show
End Sub

Sub shows3()
UserForm3.Show
End Sub


Hope this helps.

[Kenneth Hobs]

You may want to explain the problem more. Sheet51 is named "1". If you are talking about running the Userforms, they work fine when I ran them manually "providing" that they were played from the sheet that they query in the Find.

[lee2boy]

Thanks gents, i have tried adding the sub routine but I still can't get it to work.
I'm a complete begineer on VBA and i'm struggling, could one of you do me the the big favour and add the detail and return the the workbook please.
Sheet 51 which is named 1 has the Cell named Materials on b7 this works fine from the lookup table LV
in cell b18 named Labour i need the the same action but from the Sl Look up table.
I will then be adding more Look up tables for Plant etc for b49 and b65 in sheet 51
I would much obliged if you could help
Regards
Lee

[Kenneth Hobs]

1!B7 does not have a drop button (data validation) in it.

[lee2boy]

if you double click on b7 it brings up the form from which i can add the stock items and costs

[CodeNinja]

Lee2boy,
I see the cells you are talking about now. The code I provided would show the userform, but there are problems with the userform2.combobox1_click() code. I am not sure what you are trying to accomplish with this, but if I comment out that code, it will open the userform.

What are you trying to accomplish when the user clicks on combobox1 -- Code to follow:

Private Sub ComboBox1_Click()
Dim c As Range
Dim Rng As Range
Dim Sh As Worksheet

Set Sh = Sheets("SL - Look-Up Tables")
Set c = Sh.Columns(1).Find(ComboBox1, lookat:=xlWhole)
Set Rng = Range(c(2), c.End(xlDown)).Resize(, 2)
ListBox1.List() = Rng.Value
End Sub

[lee2boy]

AS PER THE USERFORM1 ITS LOOKING FOR THE HEADINGS LISTED IN PURPLE UNDER THE NAME "DATA FOR COMBOBOX" IN THE ACTUAL SL LOOK UP TABLE
ALL I HAVE DONE IS COPIED THE THE FIRST USERFORM1 FOR THE LV LOOK UP TABLE WHICH WORKS FINE
REGARDS
LEE