**Solved** Userform Help

[Von Pookie]

Ok. I give. I've gotten so far by myself, and was doing so well without having to ask a question, but nooooo...this had to "stump me", as it were.

On my userform, I will enter a page number in TextBox3, and select a year from ComboBox1.

So if I enter 123 in the textbox (however this can be a 1 to 3 digit number), then choose either ('00) or ('01) from the combobox.

How can I have these both end up in cell C3 as 123 ('00)? I've been messing with it all afternoon, and am severely stuck.

Thanks,

_________________
~*Kristy*~

"Catapultam habeo. Nisi pecuniam omnem mihi dabis, ad caput tuum saxum immane mittam."



[ This Message was edited by: Von Pookie on 2002-05-21 12:54 ]

[Jack in the UK]

Hi Kristy

your need VBA to do this can you post you code so far ill have a play see if i can make something usable

[Von Pookie]

I wasn't sure if anyone would need the code or not. Here you go, Jack:

Private Sub UserForm_Click()



    TextBox1.SetFocus

    

End Sub



Private Sub TextBox3_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)

  If KeyCode = 13 Then

    Call CommandButton1_Click

  End If

End Sub

Private Sub ComboBox1_Click()

    

    ComboBox1.RowSource = ("Products!I1:I2")

       Application.OnTime Now, "DropDown_ComboBox"

    

        

End Sub



Private Sub CommandButton1_Click()



 ActiveSheet.Unprotect



    Range("$A$3").Select

        Selection.EntireRow.Insert



    Range("$A$3") = TextBox1.Value

    Range("$B$3") = TextBox2.Value

    Range("$C$3") = TextBox3.Value

    Range("$D$3") = TextBox4.Value



    

    If TextBox3.Value = "" And ComboBox1.Value = "" Then

        Range("$C$3").Select

               ActiveCell.FormulaR1C1 = "Not Listed"

        Range("A3:D3").Select

            With Selection.Interior

                .ColorIndex = 15

                .Pattern = xlSolid

            End With

     End If

    

    Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _

        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

    Range("A2").Select

        

    

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True



    Dim ctl As Control



        For Each ctl In Me.Controls

            If TypeName(ctl) = "TextBox" Then ctl.Text = ""

            If TypeName(ctl) = "ComboBox" Then ctl.Text = ""

        Next ctl

        

    TextBox1.SetFocus



End Sub





Private Sub CommandButton2_Click()



Dim ctl As Control



        For Each ctl In Me.Controls

            If TypeName(ctl) = "TextBox" Then ctl.Text = ""

            If TypeName(ctl) = "ComboBox" Then ctl.Text = ""

        Next ctl

        



    UserForm1.Hide

    TextBox1.SetFocus

          

End Sub

Private Sub CommandButton3_Click()



    Dim ctl As Control



        For Each ctl In Me.Controls

            If TypeName(ctl) = "TextBox" Then ctl.Text = ""

            If TypeName(ctl) = "ComboBox" Then ctl.Text = ""

        Next ctl



    TextBox1.SetFocus



End Sub

_________________
~*Kristy*~

"Catapultam habeo. Nisi pecuniam omnem mihi dabis, ad caput tuum saxum immane mittam."



[ This Message was edited by: Von Pookie on 2002-05-20 16:05 ]

[NateO]

Hi Kristy, does changing

Range("$C$3") = TextBox3.Value

to

Range("$C$3") = TextBox3.Value & " " & ComboBox1.Value

Help?

[Von Pookie]

Yay! Yes it did! I was closer today than I thought I was

But, now, after I click ok (commandbutton1) and add it to my listing, then I click cancel (commandbutton2) to "hide" the form and now it's telling me: "the macro test2.xls'!DropDown_ComboBox' cannot be found."

Obviously, it didn't do this before. Why would it start this now?

Strange...I just added it to the exact same workbook here at work, and it worked fine. Methinks the laptop may be a bit screwy with VBA (it is running a different version of Excel )

Thanks, Nate

_________________
~*Kristy*~

"Catapultam habeo. Nisi pecuniam omnem mihi dabis, ad caput tuum saxum immane mittam."



[ This Message was edited by: Von Pookie on 2002-05-21 06:02 ]

[NateO]

Hello Kristy,

the form and now it's telling me: "the macro test2.xls'!DropDown_ComboBox' cannot be found."

It sounds to me like me.controls is associating itself with another file that is located in your office, but not at home (at work your actually clearing several forms when you click this). Perhaps use:

For Each Ctl In UserForm1.Controls

instead.

[Von Pookie]

The most amazing thing just happened. I started getting that message here at work, too, right before I saw this response. What timing

Now it's started to do it after I added a message box to come up if certain textboxes are blank. It was working fine until I got the message box to work like I want it to, of course. What I added before it started is in red. I've changed all of the me.controls to UserForm1.controls, as well. Still doing it.

Private Sub CommandButton1_Click()



If TextBox1.Value = "" And TextBox2.Value = "" Then

        MsgBox "Please enter an Item Description and Item Number to continue."

            TextBox1.SetFocus

    

    Else



 ActiveSheet.Unprotect



    Range("$A$3").Select

        Selection.EntireRow.Insert



    Range("$A$3") = TextBox1.Value

    Range("$B$3") = TextBox2.Value

    Range("$C$3") = TextBox3.Value & " " & ComboBox1.Value

    Range("$D$3") = TextBox4.Value



    

    If TextBox3.Value = "" And ComboBox1.Value = "" Then

        Range("$C$3").Select

               ActiveCell.FormulaR1C1 = "Not Listed"

        Range("A3:D3").Select

            With Selection.Interior

                .ColorIndex = 15

                .Pattern = xlSolid

            End With

     End If

    

    Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _

        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

    Range("A2").Select

        

    

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True



    Dim ctl As Control



        For Each ctl In UserForm1.Controls

            If TypeName(ctl) = "TextBox" Then ctl.Text = ""

            If TypeName(ctl) = "ComboBox" Then ctl.Text = ""

        Next ctl

        

    TextBox1.SetFocus



End If

End Sub

Thanks,

_________________
~*Kristy*~

"Catapultam habeo. Nisi pecuniam omnem mihi dabis, ad caput tuum saxum immane mittam."



[ This Message was edited by: Von Pookie on 2002-05-21 09:24 ]

[NateO]

How are you launching the userform (if by button, try reassigning the button's procedure)?

I can't see anything in your code linking externally.

[NateO]

Found the issue in your original code posting in the combobox1_click procedure:

Application.OnTime Now, "DropDown_ComboBox"

Must be a procedure that's no longer available, you'll want to correct this code, if not necessary, eliminate it.

_________________
Cheers, NateO

[ This Message was edited by: NateO on 2002-05-21 10:50 ]

[Von Pookie]

You snuck in there on me

I deleted the line, and ended up changing where the source row is, which I changed in the code and in the properties for the combobox.

So, now I have

ComboBox1.RowSource = ("Products!I1:K1")

And nothing is coming up when I click on the drop down arrow in the combobox.

On the bright side, I'm not getting that error anymore


_________________
~*Kristy*~

"Catapultam habeo. Nisi pecuniam omnem mihi dabis, ad caput tuum saxum immane mittam."



[ This Message was edited by: Von Pookie on 2002-05-21 11:06 ]