×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

#### Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

# ..Get Excel to Extra! Droplist options. Why is this code not working?

## ..Get Excel to Extra! Droplist options. Why is this code not working?

(OP)
I know that I have approached you with this question before. This code I am presenting reflects my continued efforts to find a solution to my original query. Is there an answer to be given? Is there a solution? It eludes me. Any further pointers would be appreciated. Thank you.

#### CODE

Dim Name1() As String
Dim NewFruit() As String

sub main

Dim obj as object

Dim objWorkbook as object

Dim Fruit() As String

Set obj=CreateObject("Excel.Application")

Set obj = Getobject("C:\Temp\Snack.xls")

set objWorkbook=obj.Worksheets("Sheet2")

CntMax=obj.ActiveSheet.Rows.Count

ReDim Fruit(30)

ReDim Name1(30)

ReDim NewFruit(20)

Cnt=1
CntA=3
CntB=1

NewCnt=0

For i = CntA To CntMax

Fruit(CntA)=Trim(objWorkBook.Range("A" + CntA))

IF CntA > 3 Then
If Fruit(CntA)=Fruit(CntA-1) then
NewFruit(CntB)=Fruit(CntA)
CntB=CntB+1
End If
ElseIF CntA = 3 then
NewFruit(CntB)=Fruit(CntA)
End If
NewFruit(CntB)=NewFruit(CntB) + Chr$(9) CntA=CntA+1 Next i FCEnd="No fruit to Offer" NewFruit(CntB)=NewFruit(CntB) & FCEnd Begin Dialog dlgOptions 154, 11, 182, 188, "Fruit to Offer" OkButton 130, 6, 50, 14, .btnOK CancelButton 130, 23, 50, 14, .btnCancel Text 48, 37, 70, 10, "Fruits" DropComboBox 46, 112, 73, 40, NewFruit(), .CB1 'CntB), .CB1 End Dialog Dim MyDialog As dlgOptions Dialog MyDialog If Err=102 then Stop End If Dim GiveFruit As String Dim Recipient As String GiveFruit=MyDialog.CB1 CntC=3 MaxCnt=CntB If GiveFruit=FCEnd then Msgbox "You don't have any fruit to give, do you?" Else For i = CntC To CntMax Givee(CntC)=Trim(objWorkBook.Range("A" + CntC)) If Givee(CntC)<>GiveFruit then CntC=CntC+1 ElseIf Givee(CntC)=GiveFruit For n=1 to MaxCnt Name1(CntB)=Trim(objWorkBook.Range("C" + CntC)) CntC=CntC+1 Name1(CntB)=Name1(CntB) + Chr$(9)
CntB=CntB+1
CntMax=CntC
Next n

End If

Next i
End If

Begin Dialog dlgOptwo 154, 11, 182, 188, "Fruit to Offer"

OkButton  130, 6, 50, 14, .btnOK
CancelButton  130, 23, 50, 14, .btnCancel
Text  48, 37, 70, 10, GiveFruit
DropComboBox  46, 112, 73, 40, Name1(), .CB1 'CntA), .CB1

End Dialog

Dim NewDlg As dlgOptwo
Dialog NewDlg

If Err=102 then
Stop
End If

Recipient=NewDlg.CB1

msgbox Recipient & " will be receiving a " & GiveFruit & " today!"

end sub

It's always better to ask stupid questions; stupid questions have been known to reduce the frequency of stupid mistakes.

### RE: ..Get Excel to Extra! Droplist options. Why is this code not working?

#### Quote:

original query. Is there an answer to be given? Is there a solution?
Please state your question(s) here, if there is ever to be an answer or solution stated here.

Skip,

Just traded in my old subtlety...
for a NUANCE!

### RE: ..Get Excel to Extra! Droplist options. Why is this code not working?

(OP)
Thanks for you speedy response Skip. I am unsure of the meaning of your statement, though. Is it with respect to the fact that I started a new thread?

It's always better to ask stupid questions; stupid questions have been known to reduce the frequency of stupid mistakes.

### RE: ..Get Excel to Extra! Droplist options. Why is this code not working?

(OP)
Please refer to thread99-1635974: Excel Get A if B And C ...Loop if you wish to respond, as it is the original request, though some variables have changed. Thank you

### RE: ..Get Excel to Extra! Droplist options. Why is this code not working?

Can that be any clearer?

Skip,

Just traded in my old subtlety...
for a NUANCE!

### RE: ..Get Excel to Extra! Droplist options. Why is this code not working?

(OP)
Thank you again, Skip. I will take it under advisement, and add this new "What not to do:" to my Forum etiquette. I'm not one to walk on others' flower beds. I have a great deal of respect for those who derive personal satisfaction with helping others; it is those individuals that make Forums such as these viable.

Can anyone tell me what the script out of range error means? I get it in this line of code...and others like it.

#### CODE

Name1(CntB)=Trim(objWorkBook.Range("C" & CntC))

I hope the next time I post, it will be with the solution! I'll keep trying.

It's always better to ask stupid questions; stupid questions have been known to reduce the frequency of stupid mistakes.

### RE: ..Get Excel to Extra! Droplist options. Why is this code not working?

#### CODE

Name1(CntB)=Trim(objWorkBook.Range("C" & CntC))
Subscript out of range usually refers to an object reference or array index.

1} CntB is LESS THAN or GREATER THAN the number of Name1 items in that collection or array.

2) CntC is LESS THAN or GREATER THAN the number of ROWS in that sheet.  And oh, BTW, a workbook object does not have range objects.  Worksheet objects have range objects.

So in Debug, check the value of these variables.  I hardle EVER use Extra Basic.  It is KLUNKY.  I do ALL my Extra coding in Excel VBA, since I am always starting or ending with data in Excel anyhow, and VBA as soooooo much easier to code in.

Skip,

Just traded in my old subtlety...
for a NUANCE!

### RE: ..Get Excel to Extra! Droplist options. Why is this code not working?

(OP)

#### Quote:

And oh, BTW, a workbook object does not have range objects. Worksheet objects have range objects.
Is this wrong then?

#### CODE

set objWorkbook=obj.Worksheets("Sheet2")

#### Quote:

Subscript out of range usually refers to an object reference or array index.
1} CntB is LESS THAN or GREATER THAN the number of Name1 items in that collection or array.

2) CntC is LESS THAN or GREATER THAN the number of ROWS in that sheet.
Thank you for this tidbit. I will have to rethink my logic, and try to resolve my problem with this in mind.

#### Quote:

Extra Basic.  It is KLUNKY.
Based on my experience so far, I will agree with that. As I mentioned before though, not everyone has excel open at any given time.

It's always better to ask stupid questions; stupid questions have been known to reduce the frequency of stupid mistakes.

### RE: ..Get Excel to Extra! Droplist options. Why is this code not working?

#### Quote:

Is this wrong then?
No, but the context is very missleading because your object is not a workbook object, but rather a worksheet object.

#### Quote:

not everyone has excel open at any given time.
Well, the user must OPEN YOUR EXCEL workbook, containing your macro in order to run it, as well as having an Extra session open.  My users do it.

Skip,

Just traded in my old subtlety...
for a NUANCE!

### RE: ..Get Excel to Extra! Droplist options. Why is this code not working?

(OP)
I did not know that I require code in excel VBA, as well as in EB. This will require a huge learning curve. So far, I have been able to retrieve data from a closed workbook. The problem has been getting StringData(x) to be interpreted as StringData(1), StringData(2), ...,StringData(10) etc.. then have this displayed in a drop-down list. I have been having this idea that I can preserve the value of the String with each loop, so that the macro understands that each string is its own entity. It's a guessing game for this novice! I'm feeling a little discouraged right now. This too shall pass. The solution is somewhere.

Thanks again, Skip.

It's always better to ask stupid questions; stupid questions have been known to reduce the frequency of stupid mistakes.

### RE: ..Get Excel to Extra! Droplist options. Why is this code not working?

#### Quote:

...require code in excel VBA, as well as in EB.
Absolutely NOT!.

I just greatly prefer to ride in comfort, with all the bells and whistles, that creep along in a heap without A/C.

Where is your list that you want to put into a dropdown?

Skip,

Just traded in my old subtlety...
for a NUANCE!

### RE: ..Get Excel to Extra! Droplist options. Why is this code not working?

(OP)

#### Quote:

Quote:
...require code in excel VBA, as well as in EB.

Absolutely NOT!.

Sorry Skip, I must have misread your post last night. I was half asleep coming off of an extra long shift. The list is in one column and an alternate list is in another column. A1 and C1. The fist list in A1, "to use my example code above", would contain an ordered list of fruits. Some of the fruits would repeat as many as 5 or  6 times; my goal is to have each fruit represented once in the list. Once a fruit is selected, an alternate list would be presented showing any and all names from C1 associated with that particular fruit. So, A1 would ultimately be visited twice throughout the time the macro is active. Once a name is selected, an email would be created to be sent to the selected fruit recipient. The big picture actually involves several individuals with specialized skills,so when a staff member comes across a situation where special knowledge is required, a screen scrape is done and the details sent to a selected recipient in an email. As for the list, everyone has a copy of it in an excel sheet, and it is updated every 2 or 3 months; not everyone will have it open at all times. So the list could change, requiring that the macro be able read the list as if it were the first time.

I'm still half asleep, so I hope this answers your question. Time to go to work! Have a great day.

It's always better to ask stupid questions; stupid questions have been known to reduce the frequency of stupid mistakes.

### RE: ..Get Excel to Extra! Droplist options. Why is this code not working?

MUCH MUCH easier to do, IMNSHO, in Excel, either using Sheet Functions or MS Query.

So you have your master list in column A & C?

What's in B?
I hope it's not an empty column!!!

You need a UNIQUE list of A's, which I guess are Fruits as the row source for your first drop down.

The selected value is the CRITERIA for a complex lookup or a query of your master list.  The result of the complex lookup of query is the row source for your second drop down.

Is this basically what you envision?

Skip,

Just traded in my old subtlety...
for a NUANCE!

### RE: ..Get Excel to Extra! Droplist options. Why is this code not working?

(OP)
Hi, Skip. I will attempt to answer all of your questions without being too wordy, or too vague.

#### Quote:

MUCH MUCH easier to do, IMNSHO, in Excel, either using Sheet Functions or MS Query.
I'm quite excited to learn about what I can do in excel. I even have a few books on excel that I am eager to delve into once I can get all the EB macros updated. In the mean time, there isn't much excitement from my coworkers about having excel open all the time, when it is used so infrequently. Some can go a whole week without ever having opened it, whereas others find themselves opening it a dozen times in one day. It's hit and miss.

#### Quote:

So you have your master list in column A & C?
It's a worksheet which containing columns A to F or G. One column contains the name of the individuals followed by their userid then their skillset then departments managers's names and so on. For My purpose, I will sort out the skillsets so they are not repeating. It shortens the list which will not be less than 45 at any given time, to a more manageable 15 or so. Originally, I was looking for, If A and B then C - C would have been the userid, which can be used in an email To: field, and the system would auto-populate it with the full email address of the individual, then it dawned on me that I don't need the userid as the email would auto-populate the email add with just the name(one less step!). So I believe this also answers ..

#### Quote:

What's in B? I hope it's not an empty column!!!

next..

#### Quote:

You need a UNIQUE list of A's, which I guess are Fruits as the row source for your first drop down.
Yes. This will be the first scan for the first dropdown.

example:
Col A
Apple -->Fruit(1)
Apple -->Fruit(2)
Banana-->Fruit(3)
Banana-->Fruit(4)
Banana-->Fruit(5)
Banana-->Fruit(6)
Cherry-->Fruit(7)
Orange-->Fruit(8)
Orange-->Fruit(9)
Orange-->Fruit(10)
Orange-->Fruit(11)
Mango -->Fruit(12)
Mango -->Fruit(13)
Peach -->Fruit(14)

Dropdown List
Apple -->FruitList(1)
Banana-->FruitList(2)
Cherry-->FruitList(3)
Orange-->FruitList(4)
Mango -->FruitList(5)
Peach -->FruitList(6)

user selects FruitList(4), then Col A is again scanned, this time looking for all instances of "Orange" - for each instance, the correlating String in Column C is given a name, Name().
Col C
Name(1)="John"
Name(2)="Susan"
Name(3)="Micheal"
Name(4)="Alice"

Dropdown
John
Susan
Micheal
Alice

user selects Name(3), and an email is sent out to Micheal advising him he gets an orange.

I know this sounds silly; it is just an example.

#### Quote:

The selected value is the CRITERIA for a complex lookup or a query of your master list.  The result of the complex lookup of query is the row source for your second drop down.
Yes again. In a nutshell. This is what I envisioned. I like to be thorough, and factor in all possiblities. I dread the idea of someone coming to me and saying,"did you consider this, or that?"

Ted

It's always better to ask stupid questions; stupid questions have been known to reduce the frequency of stupid mistakes.

### RE: ..Get Excel to Extra! Droplist options. Why is this code not working?

(OP)
I believe I may have figured it out, finally. I kept thinking I had to have two separate variables and comparing them. This is what kept giving me errors. This code is not yet tested, and is so simplified. I think it will actually work.

#### CODE

Dim Fruit() As String
...
...
P=1
i=3
Cnt=3
Redim Fruit(50)
Fruit(p)=xlSheet.Range("A" & i)
Redim Preserve Fruit(p)
Do While xlsheet.Range("A" & Cnt)<>""
i=i+1
Do While xlSheet.Range("A" & i)<>Fruit(p)
i=i+1
Loop
p=p+1
fruit(p)=xlSheet.range("A" & i)
redim preserve fruit(p)
Cnt=Cnt+1
Loop
..
..
DropComboBox 10, 40, 15, 57, Fruit(), .DCB

This has been a pain in my side. I am crossing my fingers to move on the the next project. If this works, there will be a few individuals on this Forum who will be seeing STARS tomorrow.

It's always better to ask stupid questions; stupid questions have been known to reduce the frequency of stupid mistakes.

#### Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

#### Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Close Box

# Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!