User Form - Order Entry Help

[Simba1]

Hi guys,

I have a user form that I'm trying to automate a little bit.

On the form I have a list of Top 10 Accounts in a combo box embedded in a frame.

In the frame beside these Top 10 Accounts I have another frame that lists the number of stores nation wide for the respective accounts.

I have the Top 10 Accounts "Row Source" linked to a hidden tab in my workbook. I also wrote down the number of stores each account has in the column beside the account name in my hidden tab.

Would anybody know how I can have the value of the number of stores on my form as a default? (Just the number of stores - the user can edit this number if he wants). Hope this isn't too confusing.

Thanks.

 

[Simba1]

I know I can't do a vlookup on a UserForm...is there another way I can lookup the values?

Account -----------------Number of Stores
______________________________________

WalMart ----------------231

Once I enter in WalMart from my combo box, the 231 automatically appears in the textbox beside it. Any ideas?

 

[Smitty]

I put thisd together for someone else, but it's relevant in your case, just change the label reference to your TextBox:

<font face=Tahoma><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> ComboBox1_Change()
    <SPAN style="color:#00007F">Dim</SPAN> c <SPAN style="color:#00007F">As</SPAN> Range
    <SPAN style="color:#00007F">Dim</SPAN> firstAddress
    
    <SPAN style="color:#007F00">'   Find Account Description that matches Account #</SPAN>
    <SPAN style="color:#00007F">With</SPAN> Worksheets("Accounts").Range("A1:A500")
    <SPAN style="color:#00007F">Set</SPAN> c = .Find(ComboBox1.Value, LookIn:=xlValues)
        <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> c <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN>
            firstAddress = c.Address
            <SPAN style="color:#00007F">Do</SPAN>
                Label8 = c.Offset(, 1)
                <SPAN style="color:#00007F">Set</SPAN> c = .FindNext(c)
            <SPAN style="color:#00007F">Loop</SPAN> <SPAN style="color:#00007F">While</SPAN> <SPAN style="color:#00007F">Not</SPAN> c <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> And c.Address <> firstAddress
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
    
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

HTH,

Smitty

 

[Simba1]

Thanks Smitty.

I haven't tried the code you have pasted above because I just got back from a meeting.

I just have 1 question -

Label8 = c.Offset(, 1)

What does the Label8 refer to?

 

[Smitty]

Label8 was the label where the result of the find was displayed for that particular form. Just swap that out for your TextBox.

Smitty

 

[Simba1]

Double Post

 

[Simba1]

Smitty, I substituted the values I needed but am not getting anything on my form. Do you see anything wrong with what I have below. Thanks for your help so far.

Private Sub ComboBox1_Change()
Dim c As Range
Dim store

With Worksheets("Calc").Range("Q1:Q31")
'Account information in Range O1:O31 and Store # Information in Range Q1:Q31

Set c = .Find(ComboBox1.Value, LookIn:=xlValues)
If Not c Is Nothing Then
store = c.store
Do
txtNumStores1 = c.Offset(, 1)
'Name of my textbox on my userform
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.store <> store
End If
End With

End Sub

 

[Simba1]

Smitty,

It turns out I am able to use a vlookup on a form, and it works perfectly. I used the following code:

Private Sub ComboBox1_Change()
txtNumStores1.Value = Application.VLookup(Me.ComboBox1.Value, Sheets("Calc").Range("O1:Q34"), 3, 0)
End Sub

I appreciate your help though.

 

[Simba1]

Hi Guys,

I get an error if I manually enter in another Account into my Combo Box or delete whatever is in there. Is there a way to add the "IF(ISERROR funtion to my code?

This is the error I get:
I get a runtime error '2147352571 (8002005)':
Could not set the Value Property. Type mismatch.

This is the code I currently have

Private Sub ComboBox1_Change()
txtNumStores1.Value = Application.VLookup(Me.ComboBox1.Value, Sheets("Calc").Range("O1:Q34"), 3, 0)
End Sub

Can anybody here help?