×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

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

Students Click Here

Jobs

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

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

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,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

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

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?



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

Can that be any clearer?

Skip,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

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

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,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

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

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,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

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

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,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

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

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

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?


Please answer ALL the questions I just posed.
 

Skip,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

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

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

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.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

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:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close