Whats wrong with this code?

**ameritecc** · 03-07-2008, 08:36 PM

I am getting various error codes that change with the way I write the Rowsource method below. I have tried all four ways of Setting the Source Range near the top of the coding, and the RowSource at the bottom that are all underlined to spot easily for someone to help me.The error message "Method range of object global failed'. comes up alot no matter which one I use, but one other error message also comes up at times that says "mismatch", and immediately takes me to my form open macro in the editor. Could someone please take a look at this code and tell me what I have done wrong.
I feel the problem is in my userform initialize code at the bottom, because if I comment it completely out in the editor the form will pop up on the worksheet (no error messages), but nothing populates the list boxes. It won't pop up under current coding. I keep getting various errors identical to the above.

[vba]
Private Sub ListBox1_Change()
Dim SourceRange As Excel.Range
Dim Val1 As String, Val2 As String, Val3 As String, Val4 As String, Val5 As String, _
Val6 As String, Val7 As String, Val8 As String, Val9 As String
If (ListBox1.RowSource <> vbNullString) Then
'Get Range that the ListBox is bound to
Set SourceRange = Range(ListBox1.RowSource)
Else
'Get first data row
'Set SourceRange = Range("Sheet32!A3:I3") Commented out
'Set SourceRange = Range("'Sheet32'!A3:I3") Commented out
'Set SourceRange = Range("A3:I3")("Sheet32") Commented out
Set SourceRange = Range("A3:I3")

Exit Sub
End If

Val1 = ListBox1.Value
'Get the value of the second column
Val2 = SourceRange.Offset(ListBox1.ListIndex, 1).Resize(1, 1).Value
'Get the value of the third column
Val3 = SourceRange.Offset(ListBox1.ListIndex, 2).Resize(1, 1).Value
Val4 = SourceRange.Offset(ListBox1.ListIndex, 3).Resize(1, 1).Value
Val5 = SourceRange.Offset(ListBox1.ListIndex, 4).Resize(1, 1).Value
Val6 = SourceRange.Offset(ListBox1.ListIndex, 5).Resize(1, 1).Value
Val7 = SourceRange.Offset(ListBox1.ListIndex, 6).Resize(1, 1).Value
Val8 = SourceRange.Offset(ListBox1.ListIndex, 7).Resize(1, 1).Value
Val9 = SourceRange.Offset(ListBox1.ListIndex, 8).Resize(1, 1).Value
'Concatenate the three values together and display them in Label1
' Label1.Caption = "Selected Data: " & vbNewLine & Val1 & " " & Val2 & " " & Val3
' Label1.Caption = " Company Name: " & Val1 & vbNewLine & "Phone: " & Val2 & vbNewLine & "Fax: " & Val3
Label1.Caption = "Company Name: " & Val1 _
& vbNewLine & "Department: " & Val2 _
& vbNewLine & "Address: " & Val3 _
& vbNewLine & "City/State/Zip Code: " & Val4 _
& vbNewLine & "Phone Number: " & Val5 _
& vbNewLine & "Fax Number: " & Val6 _
& vbNewLine & "Cell Phone Number: " & Val7 _
& vbNewLine & "Contact Name: " & Val8 _
& vbNewLine & "Email Address: " & Val9 _

'Clean Up
Set SourceRange = Nothing
End Sub

VBA Code in my userform initialize
Private Sub UserForm_Initialize()
'Clean data range
'DeleteBlankRows
'DeleteBlankColumns

'Set properties of listbox1
With Me.ListBox1
.BoundColumn = 1
.ColumnCount = 1
.ColumnHeads = False
.TextColumn = True
'.RowSource = "Sheet32!A3:I" & xlLastRow("Sheet32") Commented Out
.RowSource = "'Sheet32'!A3:I" & xlLastRow("Sheet32")
'.RowSource = "A3:I" & xlLastRow("Sheet32") Commented Out
.ListStyle = fmListStyleOption
.ListIndex = 0

End With
End Sub
[/vba]

Edit Lucas: If you use the VBA button instead of quote it will format your code just as in the VBE. I have fixed the code above for you.....

**Bob Phillips** · 03-08-2008, 02:47 AM

That code works fine for me.

Of these

[vba]
Set SourceRange = Range("Sheet32!A3:I3") 'Commented out
Set SourceRange = Range("'Sheet32'!A3:I3") 'Commented out
Set SourceRange = Range("A3:I3")("Sheet32") 'Commented out
Set SourceRange = Range("A3:I3")
[/vba]

The first two are the same and work fine.

The third is just wrong syntax, and will never work.

The fourth works, but assumes the activesheet, you are not being explicit.

Of these

[vba]

.RowSource = "Sheet32!A3:I" & xlLastRow("Sheet32") 'Commented Out
.RowSource = "'Sheet32'!A3:I" & xlLastRow("Sheet32")
.RowSource = "A3:I" & xlLastRow("Sheet32") 'Commented Out
[/vba]

all three work for me, the first two are the same eseentially, you onlt need to surreound the sheet name with single quotes if it has embedded spaces. The rhird again is not explicit about the sheet.

**ameritecc** · 03-08-2008, 05:23 AM

I commented out the code you said would not work and added statements to the same line in case I come accross this again in another bolock of code that I may end up using in the future. I will know which one doesn't work and the one that is for active sheet only.
I am using only those now that you said worked for you, and just got an error message that says;

Runtime Error 9 "Subscript out of range"

I felt these were fine also, especially since my form will pop up without errors stating

mismatch

or

method range of glabal failed

, when I comment out the form initialize code below:

[VBA]
Private Sub UserForm_Initialize()
'Clean data range
'DeleteBlankRows
'DeleteBlankColumns

'Set properties of listbox1
With Me.ListBox1
.BoundColumn = 1
.ColumnCount = 1
.ColumnHeads = False
.TextColumn = True
.RowSource = "Sheet32!A3:I" & xlLastRow("Sheet32")
'.RowSource = "'Sheet32'!A3:I" & xlLastRow("Sheet32")
ListStyle = fmListStyleOption
.ListIndex = 0

End With
End Sub
[/VBA]
I am going to leave this thread open for now, hoping that something else will be spotted by someone that could be the problem.
Thanks XLD for providing your input. Sometimes solving these things is as simply starting with the process of elimination. I know two lines of code now that won't work and they are gone.

**Bob Phillips** · 03-08-2008, 06:49 AM

WHy not post the worbook, it will simplify matters.

**mikerickson** · 03-09-2008, 01:19 PM

Have you tried putting the code in a normal module and having the ListBox1_Change event call that routine.

Range("Sheet1!A1:A20") causes problems when executed in another sheet's module.

Also SourceRange is a property of a Parameter object, using it as a variable name might be part of the problem.

**Norie** · 03-09-2008, 01:29 PM

Could you please stop cross posting?

It's getting a bit annoying.

Also you don't actually seem to be answering any questions asked regarding your code.

**ameritecc** · 03-09-2008, 08:13 PM

Well, I thought I was answering all the questions, and also trying to make headway myself by taking advise given me and working with it. I answered those today on the other Forum and the only reason this was posted today in this form is because I read a response here from a member to another that requested if we post on other forums and get answers that we should at least share that information where the thread started so others could benefit. It also said that we should at least post the URL where the thread is.
Quite frankly I wasn't getting alot of response here so I decided to go to another one also.
I'm not a VBA GURU, nor do I know all the rules yet about posting. I have been at this program I developed for seven years and only recently started posting anything. I don't expect all this to be done for me, so when I get suggestions and ideas, I work on them for ours trying to learn. I post the code requested of me, and try to answer all the questions.
It has been requested of me that I post this workbook and I cannot do that. This workbook is 50 megabytes in size and represents seven years of hard private work that I intend to market. It has already been sold many times over for private customers, yet I strive to to make it better through VBA. It started as an excell program and grew from there. Posting the entire workbook would allow me no protection in ownership until it is marketed. I don't own the code, but no one does. But I do own the design, the database, the creation of all that it achieves.
I have posted jpg worksheets, jpg forms and written what I consider alot of text describing my issues.
I apologize if your annoyed, but I am just looking to learn and answers. I just went back through my post and can't find what I didn't answer. I may have answered them crudely, but that come only from inexperience.
My question is this.....when we are getting some answers in one place yet maybe not enough, is it a rule not to go to other forums?

**lucas** · 03-09-2008, 08:41 PM

Well, I thought I was answering all the questions, and also trying to make headway myself by taking advise given me and working with it. I answered those today on the other Forum and the only reason this was posted today in this form is because I read a response here from a member to another that requested if we post on other forums and get answers that we should at least share that information where the thread started so others could benefit. It also said that we should at least post the URL where the thread is.
Quite frankly I wasn't getting alot of response here so I decided to go to another one also.
I'm not a VBA GURU, nor do I know all the rules yet about posting. I have been at this program I developed for seven years and only recently started posting anything. I don't expect all this to be done for me, so when I get suggestions and ideas, I work on them for ours trying to learn. I post the code requested of me, and try to answer all the questions.
It has been requested of me that I post this workbook and I cannot do that. This workbook is 50 megabytes in size and represents seven years of hard private work that I intend to market. It has already been sold many times over for private customers, yet I strive to to make it better through VBA. It started as an excell program and grew from there. Posting the entire workbook would allow me no protection in ownership until it is marketed. I don't own the code, but no one does. But I do own the design, the database, the creation of all that it achieves.
I have posted jpg worksheets, jpg forms and written what I consider alot of text describing my issues.
I apologize if your annoyed, but I am just looking to learn and answers. I just went back through my post and can't find what I didn't answer. I may have answered them crudely, but that come only from inexperience.
My question is this.....when we are getting some answers in one place yet maybe not enough, is it a rule not to go to other forums?

All well and good ameritecc........could you please point to the link in this thread that says "This question has been cross posted here" with the link?

As for replying to questions....I can see in post #4 that Bob has asked you to post the workbook if possible and you did not reply to that until you were prompted by Norie........

We have a reputation for being one of the friendly forums. Please help us so that we can help you. The faq is available at the top of each page. Please take a minute or two to read through it.

Cross posting in itself is not a problem....it is only when you do not let everyone know what is going on and provide links that it becomes inconvenient.....that is your responsiblility.
Also please read this short note on why cross posting is a problem if you don't provide links.

Addressing your concern over protecting your workbook.....you may only be fooling yourself if you think you can keep the contents and code a total secret....Excel is not a secure enviornment......almost anything you can think to do to protect your workbook can be broken.....probably in minutes. You are better off dealing with legitimate business people rather than trying to protect yourself from unscrupulous people.

Thanks for your understanding and cooperation.

**ameritecc** · 03-09-2008, 09:43 PM

I am dealing with legitimate business people, and I am fully aware of the drawbacks of excell and the limited protection it provides. Excell however is the most widely used program for my type of work, and in protecting my design I have taken simply a different approach. Make the internal operation of it so sophisticated that tracking each and every excell formula would be a hackers nightmare and just make it not worth the time. Anyone could get into it, but the hiarchy of how it pulls information and anaylysis it, would not be of interest to the average hacker, and it is built for one medium only.
I posted two answers today, or at least I thought I posted them correctly. I may be rushing through this too quickly, but as soon as I post reply at the botom of the quote, I hit the return button at the top and go back to the forum or where I was. I may have returned before hitting the button. I am going to look through other post also, where I know I said the File size was too large and stated the size. I will post it if so.
It was me also that just stated in my previous post about what I read today about thread answers in other forums and posting the URL. I said that before you instructed me. This is what I told Norie, and I asked the question about cross posting, because what I read today said to share the answers, which makes sense.
I am new to this, and I am spending more time finding out what I am doing wrong, than what is the right way.
Maybe I am off track here, but I thought this was about code, and the proper sharing of syntax for it. Read through my post again and you will not see alot of that. I have written so far some of the longest descriptions of my issues that are on this forum. I have provided jpegs, code, and underlined code lines for quicker spotting. I don't see all the request on my part being answered either. Some have, some haven't. That is understandable. Time is not always available, and that goes for me also.
I go to other forums to get answers for the same problem, if I am not getting them. I have not complained , just taken it upon myself to keep searching.
As far as friendly, I am the one that said and I quote "I apologize". I was refered to as annoying, and my response was more friendly than that. I also said my inexperience at this, at code, at the forum rules is the reason I may be lacking.
I have studied code here, answers, played with, manipulated, solved many of my own code problems long before I ever posted one question. Roughly two weeks ago is the first time I ever posted anything.
I have read your FAQ and everything else I could here, trying to do this correctly. I probably just need to go back to studying the code issues here rather than post anything, because I don't have the time to keep writing long letters to justify my being here.

**lucas** · 03-09-2008, 10:04 PM

No need to write long letters.....just respond to the questions and post links when you have posted the questions somewhere else also....that's all.

If you have read the link I posted here then you should understand a little better......doesn't matter if we come on this a week from now, if we can help we will but if there is a link to check we would like to know where you are on it at the time.....it's just courtesy....that's all. Remember, you are the one seeking help and there is good help available here....

**Bob Phillips** · 03-10-2008, 01:19 AM

I think that you are missing the point here.

You are asking for help in developing a program that you want to make money from. The people who help will get none of that remuneration.

Okay, nothing intrinsically wrong in that, anyone who posts asking for help in a program that they use to make work easier is doing basically the same thing, by making work better they help their standing, which is where they earn their crust.

But , and this is the key point to me, we do it free, we give a huge amount of time and knowledge, so we can legitimately ask they the poster helps us, gives us something that we can work with, not put the onus on us to re-create situations, create test data etc.

You say that you don't have time to explain yourself, but seem quite happy to just soak up our time, as if that is something that doesn't matter, we can work it all out and try and keep up with everything everywhere.

Cross-posting is one of those things that makes life harder, and takes more time. Adding a link may make you feel absolved, you have told us, but why should we have to go over to other forums to see what is going on there, what is being said, try and catch up with those attempts. I once spent a lot of time helping one guy, only to find that he had posted elsewhere and gotten an answer that he said was perfect. SO my time was wasted IMO. Since then I have decided that as and when I know someone has cross-posted, I am out of it, I am not going to go checking the other place. So posting a link is important to me, it allows me to exercise my prerogative to not help.

**ameritecc** · 03-10-2008, 08:38 AM

I am not for sure that what I just wrote was really read. I said "I don't have he time to keep writing to justify mtself being here." That is not saying I don't have the time to explain my issues. I do explain them.
First I will address Lucas and his comments to me as I said I would about the postings he was looking for that were missing.
Look under my Post "Still Trying to Figure this Out (Over My head). At 5:56 pm on 03-07-08 I posted the URL Link for the continuation of the thread for all users to follow. In that same thread I also mention the file size of the workbook being 50 megs and that I can upload jpegs of forms etc to make this easier. Now these were posted before I received the letter from you Lucas, just as I said I did.
What is wrong here is that I evidently posted the URL thread in a relative thread issue and not in the correct thread. There again, I did this however as I said I did and before I was questioned on it. I also did it because I did read about the significance of it and have acknowledge already it's importance.

Secondly XLD, I am not asking users here to help me develop a program that will make me money and not them. I have spent seven years on this, and just two weeks ago started posting anything....anywhere. 90% of this program is done with excell and every bit of it I accomplished by myself without forums, without third party involvement. VBA is now being used in enhancing an already viable, already widely used, and almost market ready application. The Database alone on this took me almost a year, and to insinuate that others have developed this for me is not proffessionaly correct.
If your telling me that this forum is for HOBBY purposes only and that all the knowledge here that is shared is for play only, and that VBA is just a
tool for hobbyist to kill time, then I am not the one waisting my time.
I assumed that every member and everyone that post questions here were developing applications large and small, to in some way further their carreer (In Dollars) or positions, or it was their iniative to become better at what they did, to achieve something. A raise from an employer to one of the participants here will not be shared by all either, a promotion or title of any significance will not either. A contract to program a specific task for a clients use will also only benefit the individual that has arranged it, and got help in developing here at this forum. I don't believe I am the only one here with a developed application in progress.
You basically acknowledged this XLD in your third paragraph of your posting.
To my credit, I have spent an enormous and probably some unproductive time simply studying code here, how it is applied and the different scanarios that it can encompass. I took the initiative to not ask anything, until I even felt I knew what I was asking. As I said my postings started rougly two week ago, and I have been coming to this site for months. Alot of the answers I get here, don't always work with my application. This is understandable, but rather than write back and say this doesn't work, I now have an idea that someone was good enough to give me, and before I write back, I incorporate it, modify it with my limited knowledge, try different approaches with it, and basically learn alot from just hours of manipulation. Sometimes I get lost and can't make it work so I write back and try to describe what is happening.
I don't think that is having others do it for me. I call it working together. I have a part also, and it is my responsibility to learn from what I am given, and try to at least apply it with work on my own.
The problem with Cross posting is something I learned on my own, through reading just a few days ago. From now on I will keep my seperate post local to each forum, but you need to look at the thread I mentioned above and see for yourself that I did at least try to do this, and before being told to.
To me and correct me if I am wrong, but mainly we deal with here blocks of code within an application that is specific to a task within that program that we are trying to solve. Posting an entire workbook and the contents therein, and information that is not even relative or dependent on that code doesn't serve a purpose. My workbook is primarily excell formulas and databases, and graphic creations that now is being enhanced by VBA for access and to make the program more user friendly. With that, most of my code request is related around forms, ranges, listboxes, textboxes etc. This program has been in real time use for three years without these enhancements. Excell is limited in this respect, but not otherwise.
Once again I offer my apology. That is the best I can do. If users here choose to not answer my request, then so be it.
I will share with all users that I learn here also. I think that some of my postings and the task that I was trying to achieve may have already helped some with similar problems. The address issue for an object located in a cell is one of them. I have searched forever for this issue in a thread in this forum and others and never come across it. It is now open to everyone and someone will utilize it the same as me.

**Bob Phillips** · 03-10-2008, 09:08 AM