..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.
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
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?
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?
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?
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,
Just traded in my old subtlety...
for a NUANCE!
RE: ..Get Excel to Extra! Droplist options. Why is this code not working?
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
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
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?
CODE
Thank you for this tidbit. I will have to rethink my logic, and try to resolve my problem with this in mind.
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?
No, but the context is very missleading because your object is not a workbook object, but rather a worksheet object.
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?
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?
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?
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?
Please answer ALL the questions I just posed.
Skip,
Just traded in my old subtlety...
for a NUANCE!
RE: ..Get Excel to Extra! Droplist options. Why is this code not working?
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.
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 ..
next..
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.
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?
CODE
...
...
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.