Storing a ComboBox selection as a variable

255 pts.
Tags:
ComboBox
Excel 2003
VBA
I have written a reporting procedure with various company code variables representing full company names. Different reports will be generated according to the company code that is plugged into the procedure. I am trying to create a ComboBox that allows the user to select the company code, which will then be used as the variable within the main procedure. I am having issues with the code recognizing the selection from the ComboBox as a string rather than the predetermined variable name. Ex.: Variables: (Company Code = Company Name) ABC1001 = "ABC Company" ABC1002 = "ABC, Inc." ABC1003 = "ABC Productions, Ltd." The ComboBox is presented to the user, allowing the user to select a company code from those available. Once the company code is selected, I have the value dropping back into the main procedure via a seperate variable called "company" Company = ComboBox1.Value Therefore, if the user selects ABC1001, Company = "ABC1001" With the company code variables I was expecting to see this... ComboBox selection returns: Company=ABC1001 Existing variable: ABC1001 = "ABC Company" Therefore: Company = "ABC Company" However, I am getting this instead... Company="ABC1001" with no connection to the variable ABC1001 because of the quotations. Is there a way to get the user's selection to load back into the main procedure without the quotations, so that it can be recognized as one of the predetermined variables rather than as a string??? Hope this makes sense. If not, let me know where I need to be more specific. Thanks!

Answer Wiki

Thanks. We'll let you know when a new response is added.

So you need to do something similar to what the INDIRECT function does with cells. I don’t know if that is possible in VBA, but how about using an array instead of the 3 variables ABC1001, ABC1002 and ABC1003 ?

Something like this:

<pre>Dim ABC100(1 To 3) As String</pre>

You could then do something like this to pass the desired text to the Company variable.

<pre>Company = ABC100(ComboBox1.ListIndex + 1)</pre>

or

<pre>Company = ABC100(Right(ComboBox1.Text, 1))</pre>

Discuss This Question: 9  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • McNitro
    This looks like it woud work IF the company codes ALL began with the prefix "ABC". However, each company code's prefix is built to correspond to the company's full name. Ex.: ABC Company: ABC1001 XYZ Company: XYZ1001 ACME, Inc.: ACM1001 ACME Business Co.: ACM1002 The company codes are created according to the accounting departments needs. It is my understanding that the company code always uses the first three characters of the company's name and the subsequent 4-digit number, as displayed by the two ACME examples. I was hoping there would be a way to get VBA to ignore/remove the "" from the combo box selection. I'll keep trying to figure it out...but continued help would be appreciated. Thanks!
    255 pointsBadges:
    report
  • Dgrainge
    You want the company code, and the company code is at the end on the string in the form AAANNNN ? Company = ABC100(Right(ComboBox1.Text, 7)) WIll give you ABC1234 or XYZ0023. Company = ABC100(Right(ComboBox1.Text, 4)) WIll give you 1234 or 0023. Company = Value(ABC100(Right(ComboBox1.Text, 4))) WIll give you 1234 or 23 (as numbers) Company = ABC100(Copy(ComboBox1.Text, len(ComboBox1.Text)-6,3)) WIll give you ABC or XYZ.
    70 pointsBadges:
    report
  • carlosdl
    Hi McNitro. I'm not sure if I am understanding correctly your point. The array name doesn't really matter. It could be anything., something like:
    Dim CompanyCodes(1 To 100) As String
    Then, you can reference the codes this way:
    Company = CompanyCodes(ComboBox1.ListIndex + 1)
    Am I missing something ? Maybe we are not taking into consideration some other factors, because we do not have them, for example, what other things you do with your ABC100<n> variables, how are you populating the combo box, why you don't populate the combo with the company name from the beginning so you don't have to use the intermediate ABC100<n> variables, etc... Let us know if you find the solution, or want more suggestions, please.
    69,175 pointsBadges:
    report
  • McNitro
    Haven't had a chance to work on this portion of my coding today. Hopefully tomorrow I can get back to it! I'll let you both know as soon as possible. :)
    255 pointsBadges:
    report
  • McNitro
    OK...I've done a bit of research on arrays, and I'm just not sure I understand how to use them at all...forget about trying to use them for what I want to do. What I'm looking for: The user is prompted to enter the company code with a UserForm consisting of a ComboBox and TextBox. If the user starts typing in A, the ComboBox will complete the listing to ACM1001, and the TextBox will populate ACME, Inc. Then if the user continues his/her entry out to ACM12, the ComboBox will complete the listing to ACM1220, and the TextBox will populate ACME Productions, Ltd. The purpose of the TextBox is to ensure the user can see whether they or about to select the correct company or not. When the user has selected the desired company code, this is fed into the main procedure as a variable tied to the client's full name. I currently have an InputBox written into the code, and the user enters the clients full name. I could use a ComboBox in this fashion, but I would rather use company codes. The accounting group will be using these more in the near future, thus, so will the rest of the company. There are a few different places where the client's full name will need to be generated (i.e. different reporting functions...after all, the macro is being written to generate reports more efficiently then manual data entry). The main reason for using client codes instead of full names is consistency. However, it will also be helpful if a client's name changes I can just change the discription of the company code variable, while the variable name itself can remain unchanged. Otherwise I will have to change the clients name throughout the written code(s). Hope this explains a little better. If not, I'll keep trying to explain. Would be MUCH easier to have a "real, live" person to help with this. LOL! Thanks again!
    255 pointsBadges:
    report
  • McNitro
    OK...Here is what I have now. Dim CoCode() As String ReDim CoCode(1 To UserForm6.ComboBox1.ListCount) Company = CoCode(Right(UserForm6.ComboBox1.Text, 7)) The code stops on the third line with a Runtime Error #13 "Type Mismatch". At time of break: UserForm6.ComboBox1.ListCount = 722 Company = EMPTY UserForm6.ComboBox1.Text = ADV1001 I tried a few of the different array formats you both presented and have come up with the same problem for each of them. I have a better grasp of how an array works now, but obviously not good enough. What am I missing???
    255 pointsBadges:
    report
  • McNitro
    I should pay attention to the text editor. Here is the current code again.
    Public CoCode() As String
    ReDim CoCode(1 To UserForm6.ComboBox1.ListCount)
    Company = CoCode(Left(UserForm6.ComboBox1.Text, 7))
    At the time of break:
    UserForm6.ComboBox1.ListCount = 722
    Company = EMPTY
    UserForm6.ComboBox1.Text = ADV1001
    
    Now maybe you can actually read it! :)
    255 pointsBadges:
    report
  • McNitro
    This question can be considered solved. I was able to use a combination of combo boxes and text boxes, along with an Access table to alleviate the issue...no array required! thanks all for your help!
    255 pointsBadges:
    report
  • carlosdl
    Great !! I was still trying to find the time to look into this... :-(
    69,175 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following