 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Programming / April 2008Select method of Range class failed
Hi all, I got this problem which is driving me nuts. In a macro i have the following line ThisWorkbook.Sheets("Home").Range("C4").Select I want to do this to be a bit more user friendly. This works fine as long as the workbook is opened in excel. However, the workbook is available on our customers website, and users that use IE7 are complaining. It seems that when they click the link, and choose open instead of save, the error message in the subject appears on the select line. I've already tried some alternatives: * first activate the sheet, then try to select the cell * select the sheet, then select cell => select on "_worksheet" failed * tried to use activate, no luck The frustrating part is that all the solutions i've tried work well in excel, but are failing when opening the workbook in IE7 :-( Any help would be greatly appreciated Hi, I don't have much experience in code on web based dooks, but how about just naming the range in Excel and then your code just becomes: Range("MyCustomNameHere").Select I would think this would be less particular about the Application object model. P.S. This is completely untested... just an idea. Cheers, Ivan. > Hi all, > [quoted text clipped - 17 lines] > > Any help would be greatly appreciated I've just tried your suggestion, but it doesn't do the trick. First attempted was done by creating a name, and trying to access it with: Application.Range("EmployerClass").Select which resulted in a Method 'Range' of object '_Application' failed Then i tried with ThisWorkbook.ActiveSheet.Range("EmployerClass").Select which resulted in the Select method of Range class error again greetz Istvan > Hi, > [quoted text clipped - 32 lines] > > > > Any help would be greatly appreciated Hi, Did you happen to try just: Range("EmployerClass").Select ? Cheers, > I've just tried your suggestion, but it doesn't do the trick. > First attempted was done by creating a name, and trying to access it with: [quoted text clipped - 46 lines] > > - Show quoted text - Tried that before, but double checked it for you. Doesn't help either, just another variation of the same error: Method 'Range' of object '_Global' failed. I just noticed that in my original post i forgot to mention that the function is located in a module, so not in a sheet. I assume that's why just using Range doesn't work. > Hi, > [quoted text clipped - 56 lines] > > > > - Show quoted text - Sorry Istvan, That's me out of ideas. I tried to recreate the problem by sticking a workbook with that code on my server, but I'm buggered if I can get IE to open it in the browser... keeps opening it with Excel. Hopefully someone else will be able to help. Cheers, > Tried that before, but double checked it for you. > Doesn't help either, just another variation of the same error: [quoted text clipped - 66 lines] > > - Show quoted text - Try the following macro in the workbook code area: Private Sub Workbook_Open() Sheets("Home").Activate Range("C4").Select End Sub REMEMBER: the workbook code area, not a standard module.  SignatureGary''s Student - gsnu200776 > Tried that before, but double checked it for you. > Doesn't help either, just another variation of the same error: [quoted text clipped - 64 lines] > > > > > > - Show quoted text - Sry guys, it took me some time to answer @Ivan: thx for your help. Opening the workbook in IE i simply do by right clicking it and open with ... It looks like excel starts up inside IE or something. @Gary's Student: This code works, as in there are no errors and it gets correctly executed. However, this is not enough. Just putting the focus on that cell when the workbook is opened is not what i'm looking for. The workbook i'm working on is one that is used to consult rates, and the macro is used to build a summary based upon the employer category. So i'd really like to set focus to that field each time the query gets executed. > Try the following macro in the workbook code area: > [quoted text clipped - 73 lines] > > > > > > > > - Show quoted text - Hello again, After quite a bit of searching, a colleague suggested to take a look at the application visibility. And yes, that did the trick. In fact, it seems that when opened in IE7, the Excel.Application.visible is set to false. This makes it impossible to select something. Following my colleague's suggestion, i made the application visible, and then minimized it. This allowed for selecting the cell. Afterwards i just reset the visibility to its original state. This causes some flickering, which is particularly noticeable when the macro is executed first time, but not so much thereafter. The strange thing is, that in numerous places i've read that a cell doesn't have to be visible in order to select it, but the application has to be apparently. It might seem to be a bit strange to pose the question and then provide the answer myself, but i just wanted to close the circle and give an answer to anyone else who might be facing similar issues. If anyone has a better solution, please post it here. Cheers Istvan > Sry guys, it took me some time to answer > [quoted text clipped - 86 lines] > > > > > > > > > > - Show quoted text - > Hello again, > [quoted text clipped - 117 lines] > > - Show quoted text - Hi Istvan, Good to hear you at least have some sort of solution. In order to reduce the flickering, does it make any difference if you minimize first, THEN set visibility? With Application .WindowState = xlMinimized .visible = True End With Cheers, Ivan. PS. Still can't get IE to open a workbook... stupid thing :) Hello Ivan Had that idea as well, but it doesn't work. It seems that you can't set a minimized state to an invisible application, which is quite normal i suppose :) But what version of IE do you have? I couldn't reproduce the problem either until i had IE7 installed on my system. greetz, Istvan > Hi Istvan, > [quoted text clipped - 11 lines] > > PS. Still can't get IE to open a workbook... stupid thing :) > > Hello again, > [quoted text clipped - 135 lines] > > - Show quoted text - Hi Istvan, I think I may have worked it out! I finally managed to get IE to open the file in the browser, and replicated your problem. The first thing I tried then worked! It appears that IE won't let you use the select method on an inactive worksheet. I changed: ThisWorkbook.Sheets("Home").Range("C4").Select to: ThisWorkbook.Sheets("Home").Activate ActiveSheet.Range("C4").Select and it worked. Let me know if it works for you. Cheers, Ivan. Ivan, I had tried it before, but just to verify i tried it once again. If struggling long enough i start doubting anything :s If i use the code you posted here, i get a "Object variable or With block variable not set" error on the ActiveSheet.Range line. This can be resolved by using ThisWorkbook.ActiveSheet. However, that results again in the Range Selection error. Maybe some more info might help you recreating the problem: On the sheet there is a button which opens a form. some input data is to be provided in the form, The ok button on the form executes the code that is causing the problem, which is located in a module. Hope this is somewhat clear. Oh, perhaps you must also know i use Excel 2002 SP3 It's great to have a workaround, but i'd really like to understand the cause of the problem ... I must admit i'm not a true macro developer, i just got this task cause i have some knowledge of how to write them (did some smaller things before). I think the understanding of this problem might increase my skill quite a bit ... Cheers Istvan ... > > > > > > > > > > I've already tried some alternatives: > > > > > > > > > > * first activate the sheet, then try to select the cell ... > I think I may have worked it out! I finally managed to get IE to open > the file in the browser, and replicated your problem. The first thing [quoted text clipped - 16 lines] > Cheers, > Ivan. > Ivan, > [quoted text clipped - 49 lines] > > - Show quoted text - Hi Istvan, Sorry that didn't solve it. With the extra info I'll see if I can work anything else out. Better sleep first though before I forget what sleep is :). I'll have a crack at it tomorrow. Cheers, > > Ivan, > [quoted text clipped - 59 lines] > > - Show quoted text - Hi Istvan, I have had another go and it is still working fine for me. I have uploaded it to a spot on the web if you want to compare it and see if it works on your machine. The file can be found here: http://icamac.googlepages.com/exceltest Let me kow how you go. Cheers, Ivan. ... > Hi Istvan, > [quoted text clipped - 8 lines] > Cheers, > Ivan. Hey Ivan, I've tried your link, but i already get a compile error, he's protesting with "Can't find project or library" on Sheet in your for each. If it can help, here you have the link to the Workbook in question: https://professional.socialsecurity.be/site_nl/Applics/drs/tauxdrs_N.xls. It's already the version with the workaround, so in order to test, you have to remove some lines at the end of the OkClick function. Thx again for your huge effort in this Greetz Istvan I found that if you have run it wrong once the program remains open. Open the task manager and delete the running excel process. This corrected my problem. I hope this helps Hello Mr Thayer, i have no idea what you are talking about :( I've tried both my program as well as Ivan's test, and neither of them left an Excel process running ... Can you please clarify a bit? greetz Istvan > I found that if you have run it wrong once the program remains open. > Open the task manager and delete the running excel process. This > corrected my problem. I hope this helps > > *** Sent via Developersdex http://www.developersdex.com *** > Hello Mr Thayer, > [quoted text clipped - 13 lines] > > - Show quoted text - Hi Istvan, Sorry I hadn't come back to the thread earlier. I did try your link the day you posted it, but the connection just times out for me, both from work and from home. Unfortunately this one still really has me stumped I'm afraid. Cheers, Ivan. Hello Ivan, That surprises me as it should be a very frequently used and always available website :(. Can you connect to the site via https://professional.socialsecurity.be/site_nl/Applics/drs/index.htm ? there in the bottom right corner you should find a link labeled "Bijdragevoetbestanden". That should give you the file. cheers Istvan ... > Hi Istvan, > [quoted text clipped - 5 lines] > Cheers, > Ivan. I have written a vbscript that kills the excel application after any code that uses excel is complete. You can call it after your code is finished. See Below. '******************************************************* Dim strComputer,objWMIService,colProcessList,objProcess Kill_Excel Sub Kill_Excel() strComputer = "." Set objWMIService = GetObject("winmgmts:" & "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2") Set colProcessList = objWMIService.ExecQuery ("Select * from Win32_Process Where Name = 'Excel.exe'") For Each objProcess in colProcessList objProcess.Terminate() Next End Sub '******************************************************* |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.