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

tedb13 · Feb 14, 2011

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.

)

SkipVought · Feb 14, 2011

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!}

tedb13 · Feb 14, 2011

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.

)

tedb13 · Feb 14, 2011

Please refer to thread99-1635974 if you wish to respond, as it is the original request, though some variables have changed. Thank you

SkipVought · Feb 14, 2011

Post your question(s) in this thread, not referenced to other threads!

Can that be any clearer?

Skip,
_{Just traded in my old subtlety...

for a NUANCE!}

tedb13 · Feb 14, 2011

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 [red]script out of range[/red] error means? I get it in this line of code...and others like it.

Code:

[blue]Name1(CntB)=Trim(objWorkBook.Range("C" & CntC))[/blue]

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.

)

SkipVought · Feb 15, 2011

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!}

tedb13 · Feb 15, 2011

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")

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.

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.

)

SkipVought · Feb 15, 2011

Is this wrong then?

No, but the context is very missleading because your object is not a workbook object, but rather a worksheet object.

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!}

tedb13 · Feb 15, 2011

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.

)

SkipVought · Feb 16, 2011

...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!}

tedb13 · Feb 16, 2011

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.

)

SkipVought · Feb 16, 2011

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?

Please answer ALL the questions I just posed.

Skip,
_{Just traded in my old subtlety...

for a NUANCE!}

tedb13 · Feb 16, 2011

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

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.

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 ..

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

next..

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.

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.

)

tedb13 · Feb 23, 2011

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 [purple]STARS[/purple] tomorrow.

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

)

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

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

tedb13

Technical User

SkipVought

Programmer

tedb13

Technical User

tedb13

Technical User

SkipVought

Programmer

tedb13

Technical User

SkipVought

Programmer

tedb13

Technical User

SkipVought

Programmer

tedb13

Technical User

SkipVought

Programmer

tedb13

Technical User

SkipVought

Programmer

tedb13

Technical User

tedb13

Technical User

Similar threads

Part and Inventory Search

Sponsor