User Form - Order Entry Help

Simba1

Board Regular
Joined
Dec 13, 2004
Messages
165
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.
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

Simba1

Board Regular
Joined
Dec 13, 2004
Messages
165
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

Legend
Joined
May 15, 2003
Messages
29,536
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

Board Regular
Joined
Dec 13, 2004
Messages
165
Thanks Smitty. (y)

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

Legend
Joined
May 15, 2003
Messages
29,536

ADVERTISEMENT

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

Board Regular
Joined
Dec 13, 2004
Messages
165

ADVERTISEMENT

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

Board Regular
Joined
Dec 13, 2004
Messages
165
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.

(y)
 

Simba1

Board Regular
Joined
Dec 13, 2004
Messages
165
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
Code:
Private Sub ComboBox1_Change()
txtNumStores1.Value = Application.VLookup(Me.ComboBox1.Value, Sheets("Calc").Range("O1:Q34"), 3, 0)
End Sub

Can anybody here help?
 

Forum statistics

Threads
1,147,675
Messages
5,742,546
Members
423,737
Latest member
tom_xls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top