What am I doing wrong?!?!

[marley]

HELP!
I have a series of UserForms that are toggled through within a proceedure that help to fill out a form in my workbook. The problem: Two of the UserForms ( identical to each other ) each have nine ComboBoxes with percentages from 10-100. If I run the macro independently through my Tools/Macros menu they work fine. However, it I run the entire procedure, when I get to these two UserForms, the ComboBoxes do not drop down. I simply get a single empty box when I click on the drop-down arrow. Both UserForms have their own Module that contains the "With" statements for giving the ComboBoxes their information. What am I doing wrong?!?

Thank you in advance,
marley

 

[Mark O'Brien]

How are you intialising the data in these comboboxes? Do you have any code on the userform to populate them?

It may be worthwhile posting some pertinent code. It doesn't sound like too tough of a nut to crack.

 

[marley]

Yes. The UserForm code is:

Private Sub CommandButton1_Click()

Range("B33").Value = TotalColors1


If TotalColors1.Value = "" Then
InkSideOne.Hide
MsgBox ("You need to enter a number of colors for Side One - (Including Varnishes and/or Coatings)"), vbExclamation, "Colors Side One"
InkSideOne.TotalColors1.SetFocus
InkSideOne.Show
Exit Sub
End If



If CheckBox1 = True Then
Range("N23") = 10
Range("O23") = Coverage1.Value
Else
Range("N23") = 0
Range("O23") = Coverage1.Value
End If

If CheckBox2 = True Then
Range("N24") = 16
Range("O24") = Coverage2.Value
Else
Range("N24") = 0
Range("O24") = Coverage2.Value
End If

If CheckBox3 = True Then
Range("N25") = 32
Range("O25") = Coverage3.Value
Else
Range("N25") = 0
Range("O25") = Coverage3.Value
End If

If CheckBox4 = True Then
Range("N26") = 48
Range("O26") = Coverage4.Value
Else
Range("N26") = 0
Range("O26") = Coverage4.Value
End If

If CheckBox5 = True Then
Range("N27") = 64
Range("O27") = Coverage5.Value
Else
Range("N27") = 0
Range("O27") = Coverage5.Value
End If

If CheckBox6 = True Then
Range("N28") = 80
Range("O28") = Coverage6.Value
Else
Range("N28") = 0
Range("O28") = Coverage6.Value
End If

If CheckBox7 = True Then
Range("N29") = 60
Range("O29") = Coverage7.Value
Else
Range("N29") = 0
Range("O29") = Coverage7.Value
End If

If CheckBox8 = True Then
Range("N30") = 25
Range("O30") = Coverage8.Value
Else
Range("N30") = 0
Range("O30") = Coverage8.Value
End If

If CheckBox9 = True Then
Range("N31") = 20
Range("O31") = Coverage9.Value
Else
Range("N31") = 0
Range("O31") = Coverage9.Value

End If
Unload InkSideOne
End Sub

and the Module code is:

Sub ShowInk1()

With InkSideOne.Coverage1
RowSource = ""
.AddItem "10"
.AddItem "20"
.AddItem "25"
.AddItem "30"
.AddItem "40"
.AddItem "50"
.AddItem "60"
.AddItem "70"
.AddItem "75"
.AddItem "80"
.AddItem "90"
.AddItem "100"
End With

With InkSideOne.Coverage2
RowSource = ""
.AddItem "10"
.AddItem "20"
.AddItem "25"
.AddItem "30"
.AddItem "40"
.AddItem "50"
.AddItem "60"
.AddItem "70"
.AddItem "75"
.AddItem "80"
.AddItem "90"
.AddItem "100"
End With

With InkSideOne.Coverage3
RowSource = ""
.AddItem "10"
.AddItem "20"
.AddItem "25"
.AddItem "30"
.AddItem "40"
.AddItem "50"
.AddItem "60"
.AddItem "70"
.AddItem "75"
.AddItem "80"
.AddItem "90"
.AddItem "100"
End With

With InkSideOne.Coverage4
RowSource = ""
.AddItem "10"
.AddItem "20"
.AddItem "25"
.AddItem "30"
.AddItem "40"
.AddItem "50"
.AddItem "60"
.AddItem "70"
.AddItem "75"
.AddItem "80"
.AddItem "90"
.AddItem "100"
End With

With InkSideOne.Coverage5
RowSource = ""
.AddItem "10"
.AddItem "20"
.AddItem "25"
.AddItem "30"
.AddItem "40"
.AddItem "50"
.AddItem "60"
.AddItem "70"
.AddItem "75"
.AddItem "80"
.AddItem "90"
.AddItem "100"
End With

With InkSideOne.Coverage6
RowSource = ""
.AddItem "10"
.AddItem "20"
.AddItem "25"
.AddItem "30"
.AddItem "40"
.AddItem "50"
.AddItem "60"
.AddItem "70"
.AddItem "75"
.AddItem "80"
.AddItem "90"
.AddItem "100"
End With

With InkSideOne.Coverage7
RowSource = ""
.AddItem "10"
.AddItem "20"
.AddItem "25"
.AddItem "30"
.AddItem "40"
.AddItem "50"
.AddItem "60"
.AddItem "70"
.AddItem "75"
.AddItem "80"
.AddItem "90"
.AddItem "100"
End With

With InkSideOne.Coverage8
RowSource = ""
.AddItem "10"
.AddItem "20"
.AddItem "25"
.AddItem "30"
.AddItem "40"
.AddItem "50"
.AddItem "60"
.AddItem "70"
.AddItem "75"
.AddItem "80"
.AddItem "90"
.AddItem "100"
End With

With InkSideOne.Coverage9
RowSource = ""
.AddItem "10"
.AddItem "20"
.AddItem "25"
.AddItem "30"
.AddItem "40"
.AddItem "50"
.AddItem "60"
.AddItem "70"
.AddItem "75"
.AddItem "80"
.AddItem "90"
.AddItem "100"
End With


InkSideOne.Show
End Sub


Thank you
Marley

 

[Mark O'Brien]

I'm assuming that you don't have any code in your "initialise" event on your userform, so I'm suggesting that you try putting this code on your userform:

<pre>
Private Sub UserForm_Initialize()
ShowInk1
End Sub</pre>

Looking at your code, it could probably be simplified. However, to directly solve your problem, I noticed that there was nothing in your code that told it to go to the "ShowInk1" to set up the form. Also, remove the line:

<pre>
InkSideOne.Show </pre>

from the "InkSide1" subroutine.

I think this solves the problem.

 

[marley]

Oh! How sweet it is. So simple, yet so elusive to me.
I am a "learning-as-I-go" VBA code writer and the only book I have is Excel Programming from MaranGraphics. It seems pretty informative, but teaches the bare minimum and then leaves the small details up to the reader. As a result, my coding is a bit "clunky" as i sort of just keep on typing until I get the desired result. This is my first project and is an ever-changing being, so I hope to get more graceful and to-the-point as I improve my skills. This message board has been helpful beyond any measurable praise.

Thank you so much!

marley

 

[Mark O'Brien]

Marley,

Glad I could help.

I would suggest that you look in your book or in VBA help, or even this board, for some examples on using loops. You could probably get rid of 75% of the lines of code you have there. We've all been there, some of us are still there.

Actually, there are a few informative websites out there. One of the best for quick examples is Dave Hawley's:

http://www.ozgrid.com

Although that's maybe not such a good one for loops since he has some kind of rational hatred of the things.

HTH