Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cannot use range objects
Hi, I'm trying to use VB to alter one worksheet based on a
roster of names in another worksheet (in the same workbook). I'm using Excel 2000 version 9.0.2720 (Windows 2000) and Visual Basic 6.0.8435. Each time the code gets to a place (any place) where it is using a range, the code stops executing and VB reports the following: "Visual Basic 400". Nothing else! Just - "400." I suspect that, since I'm new to VB, I'm not setting things up correctly. Here is a typical function (see *** for the line where things are breaking down): Private Sub addNewList(ByVal numEmpl As Integer) Dim mySheetName As String Dim mySheet As Worksheet Dim myRange As Range Dim numOfWeeks As Integer Dim numOfShifts As Integer Dim baseCell As Range Dim cellFormula As String Dim initials As String 'Initialize variables mySheetName = Worksheets("ROSTER").Range("E7").Value Set mySheet = Worksheets(mySheetName) numOfWeeks = Worksheets("Roster").Range("E10").Value numOfShifts = numOfWeeks * 17 'Get to our starting point mySheet.Select Set myRange = Range("B5") *** myRange.Select If anyone can help, I would greatly appreciate it. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cannot use range objects
Jason,
Your code did not generate any errors when I tried it out. But, there is a Knowledge Base Article - 213835 about error code 400: http://support.microsoft.com/default...213835&Product =xlw2K It doesn't seem to apply to your code, but perhaps you have an event that is fired when your activate your sheet? SYMPTOMS In Microsoft Excel 2000, when you use the Microsoft Visual Basic ShowDataForm method, you may receive one of the following error messages: Run-time error '1004': ShowDataForm method of Worksheet class failed -or- 400 CAUSE This behavior can occur because Excel is unable to find a data list on the worksheet that you specified in the Visual Basic statement. HTH, Bernie MS Excel MVP "jason" wrote in message ... Hi, I'm trying to use VB to alter one worksheet based on a roster of names in another worksheet (in the same workbook). I'm using Excel 2000 version 9.0.2720 (Windows 2000) and Visual Basic 6.0.8435. Each time the code gets to a place (any place) where it is using a range, the code stops executing and VB reports the following: "Visual Basic 400". Nothing else! Just - "400." I suspect that, since I'm new to VB, I'm not setting things up correctly. Here is a typical function (see *** for the line where things are breaking down): Private Sub addNewList(ByVal numEmpl As Integer) Dim mySheetName As String Dim mySheet As Worksheet Dim myRange As Range Dim numOfWeeks As Integer Dim numOfShifts As Integer Dim baseCell As Range Dim cellFormula As String Dim initials As String 'Initialize variables mySheetName = Worksheets("ROSTER").Range("E7").Value Set mySheet = Worksheets(mySheetName) numOfWeeks = Worksheets("Roster").Range("E10").Value numOfShifts = numOfWeeks * 17 'Get to our starting point mySheet.Select Set myRange = Range("B5") *** myRange.Select If anyone can help, I would greatly appreciate it. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cannot use range objects
Bernie (et al.), Thanks for the replies. To Bernie in particular, I looked up that error code, also, and decided that it couldn't apply to me; maybe I'm wrong about that, but I don't see how. Another thought has occurred to me. I was not able to do the installation of Windows 2000 on my machine when it was provided to me. Could it be that the libraries that support use of range objects have not been installed? I know, for example, that the help system for Visual Basic has not been installed. I've got the object browser, project browser, basic editing, etc. But maybe I don't have the libraries. Is it possible to install MS Office and not install the components necessary to run VB (or, at any rate, run it at a high level)? Thanks for your help. If I cannot get the range objects to work, I may try something else, perhaps what Don was suggesting. Jason -----Original Message----- Jason, Your code did not generate any errors when I tried it out. But, there is a Knowledge Base Article - 213835 about error code 400: http://support.microsoft.com/default.aspx?scid=kb;en- us;213835&Product =xlw2K It doesn't seem to apply to your code, but perhaps you have an event that is fired when your activate your sheet? SYMPTOMS In Microsoft Excel 2000, when you use the Microsoft Visual Basic ShowDataForm method, you may receive one of the following error messages: Run-time error '1004': ShowDataForm method of Worksheet class failed -or- 400 CAUSE This behavior can occur because Excel is unable to find a data list on the worksheet that you specified in the Visual Basic statement. HTH, Bernie MS Excel MVP "jason" wrote in message ... Hi, I'm trying to use VB to alter one worksheet based on a roster of names in another worksheet (in the same workbook). I'm using Excel 2000 version 9.0.2720 (Windows 2000) and Visual Basic 6.0.8435. Each time the code gets to a place (any place) where it is using a range, the code stops executing and VB reports the following: "Visual Basic 400". Nothing else! Just - "400." I suspect that, since I'm new to VB, I'm not setting things up correctly. Here is a typical function (see *** for the line where things are breaking down): Private Sub addNewList(ByVal numEmpl As Integer) Dim mySheetName As String Dim mySheet As Worksheet Dim myRange As Range Dim numOfWeeks As Integer Dim numOfShifts As Integer Dim baseCell As Range Dim cellFormula As String Dim initials As String 'Initialize variables mySheetName = Worksheets("ROSTER").Range("E7").Value Set mySheet = Worksheets(mySheetName) numOfWeeks = Worksheets("Roster").Range("E10").Value numOfShifts = numOfWeeks * 17 'Get to our starting point mySheet.Select Set myRange = Range("B5") *** myRange.Select If anyone can help, I would greatly appreciate it. . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cannot use range objects
Jason:
I don't think so. Installing the VBA *Help* files is definitely an option that needs to be turned on during a custom install of Office, but the files that VBA requires to function are always installed, as is the VB development environment (VBE): the Object Browser, etc. If they weren't, there would be a lot of workplaces that would gladly chose to cripple their user's installations of Office "for Security purposes" rather than relying on "Disable/Enable macros" options. Brrrrrr. That's a scary thought. -- George Nicholson Remove 'Junk' from return address. "jason" wrote in message ... Bernie (et al.), Thanks for the replies. To Bernie in particular, I looked up that error code, also, and decided that it couldn't apply to me; maybe I'm wrong about that, but I don't see how. Another thought has occurred to me. I was not able to do the installation of Windows 2000 on my machine when it was provided to me. Could it be that the libraries that support use of range objects have not been installed? I know, for example, that the help system for Visual Basic has not been installed. I've got the object browser, project browser, basic editing, etc. But maybe I don't have the libraries. Is it possible to install MS Office and not install the components necessary to run VB (or, at any rate, run it at a high level)? Thanks for your help. If I cannot get the range objects to work, I may try something else, perhaps what Don was suggesting. Jason -----Original Message----- Jason, Your code did not generate any errors when I tried it out. But, there is a Knowledge Base Article - 213835 about error code 400: http://support.microsoft.com/default.aspx?scid=kb;en- us;213835&Product =xlw2K It doesn't seem to apply to your code, but perhaps you have an event that is fired when your activate your sheet? SYMPTOMS In Microsoft Excel 2000, when you use the Microsoft Visual Basic ShowDataForm method, you may receive one of the following error messages: Run-time error '1004': ShowDataForm method of Worksheet class failed -or- 400 CAUSE This behavior can occur because Excel is unable to find a data list on the worksheet that you specified in the Visual Basic statement. HTH, Bernie MS Excel MVP "jason" wrote in message ... Hi, I'm trying to use VB to alter one worksheet based on a roster of names in another worksheet (in the same workbook). I'm using Excel 2000 version 9.0.2720 (Windows 2000) and Visual Basic 6.0.8435. Each time the code gets to a place (any place) where it is using a range, the code stops executing and VB reports the following: "Visual Basic 400". Nothing else! Just - "400." I suspect that, since I'm new to VB, I'm not setting things up correctly. Here is a typical function (see *** for the line where things are breaking down): Private Sub addNewList(ByVal numEmpl As Integer) Dim mySheetName As String Dim mySheet As Worksheet Dim myRange As Range Dim numOfWeeks As Integer Dim numOfShifts As Integer Dim baseCell As Range Dim cellFormula As String Dim initials As String 'Initialize variables mySheetName = Worksheets("ROSTER").Range("E7").Value Set mySheet = Worksheets(mySheetName) numOfWeeks = Worksheets("Roster").Range("E10").Value numOfShifts = numOfWeeks * 17 'Get to our starting point mySheet.Select Set myRange = Range("B5") *** myRange.Select If anyone can help, I would greatly appreciate it. . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cannot use range objects
Hi Jason, just a thought , I had some code that wouldnt work once and I was
told to set the references in VB etc. you may need to enable the references in the VB environment to include "Visual Basic for Applications" and "Microsoft Object Library" Ie. Open the VB workspace then select Tools - References and click the check box for the above references. Fred "jason" wrote in message ... Hi, I'm trying to use VB to alter one worksheet based on a roster of names in another worksheet (in the same workbook). I'm using Excel 2000 version 9.0.2720 (Windows 2000) and Visual Basic 6.0.8435. Each time the code gets to a place (any place) where it is using a range, the code stops executing and VB reports the following: "Visual Basic 400". Nothing else! Just - "400." I suspect that, since I'm new to VB, I'm not setting things up correctly. Here is a typical function (see *** for the line where things are breaking down): Private Sub addNewList(ByVal numEmpl As Integer) Dim mySheetName As String Dim mySheet As Worksheet Dim myRange As Range Dim numOfWeeks As Integer Dim numOfShifts As Integer Dim baseCell As Range Dim cellFormula As String Dim initials As String 'Initialize variables mySheetName = Worksheets("ROSTER").Range("E7").Value Set mySheet = Worksheets(mySheetName) numOfWeeks = Worksheets("Roster").Range("E10").Value numOfShifts = numOfWeeks * 17 'Get to our starting point mySheet.Select Set myRange = Range("B5") *** myRange.Select If anyone can help, I would greatly appreciate it. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cannot use range objects
Alas, I've tried adding both of those, and it didn't fix
the problem. Good thought, though. I even added a few libraries that I thought *might* be relevant, but no luck. Ah well. Back to the drawing board. Jason -----Original Message----- Hi Jason, just a thought , I had some code that wouldnt work once and I was told to set the references in VB etc. you may need to enable the references in the VB environment to include "Visual Basic for Applications" and "Microsoft Object Library" Ie. Open the VB workspace then select Tools - References and click the check box for the above references. Fred "jason" wrote in message ... Hi, I'm trying to use VB to alter one worksheet based on a roster of names in another worksheet (in the same workbook). I'm using Excel 2000 version 9.0.2720 (Windows 2000) and Visual Basic 6.0.8435. Each time the code gets to a place (any place) where it is using a range, the code stops executing and VB reports the following: "Visual Basic 400". Nothing else! Just - "400." I suspect that, since I'm new to VB, I'm not setting things up correctly. Here is a typical function (see *** for the line where things are breaking down): Private Sub addNewList(ByVal numEmpl As Integer) Dim mySheetName As String Dim mySheet As Worksheet Dim myRange As Range Dim numOfWeeks As Integer Dim numOfShifts As Integer Dim baseCell As Range Dim cellFormula As String Dim initials As String 'Initialize variables mySheetName = Worksheets("ROSTER").Range("E7").Value Set mySheet = Worksheets(mySheetName) numOfWeeks = Worksheets("Roster").Range("E10").Value numOfShifts = numOfWeeks * 17 'Get to our starting point mySheet.Select Set myRange = Range("B5") *** myRange.Select If anyone can help, I would greatly appreciate it. . |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cannot use range objects
Another couple of guesses:
1. Did you ever declare a variable called "range"? 2. You may want to run Rob Bovey's code cleaner: You can find it at: http://www.appspro.com/ 3. Is your code behind a worksheet? I got a 1004 (not 400) error when I tried to select a range that wasn't fully qualified. (Unqualified ranges belong to the worksheet owning the code--even if you've selected another sheet first.) with mySheet .Select Set myRange = .Range("B5") end with myRange.Select So now that .range("b5") belongs to mySheet--not the sheet owning the code. 4. When you were adding references, did you notice if any reference was missing? If yes, try removing it (or finding it) and see if that helps. Jason wrote: Alas, I've tried adding both of those, and it didn't fix the problem. Good thought, though. I even added a few libraries that I thought *might* be relevant, but no luck. Ah well. Back to the drawing board. Jason -----Original Message----- Hi Jason, just a thought , I had some code that wouldnt work once and I was told to set the references in VB etc. you may need to enable the references in the VB environment to include "Visual Basic for Applications" and "Microsoft Object Library" Ie. Open the VB workspace then select Tools - References and click the check box for the above references. Fred "jason" wrote in message ... Hi, I'm trying to use VB to alter one worksheet based on a roster of names in another worksheet (in the same workbook). I'm using Excel 2000 version 9.0.2720 (Windows 2000) and Visual Basic 6.0.8435. Each time the code gets to a place (any place) where it is using a range, the code stops executing and VB reports the following: "Visual Basic 400". Nothing else! Just - "400." I suspect that, since I'm new to VB, I'm not setting things up correctly. Here is a typical function (see *** for the line where things are breaking down): Private Sub addNewList(ByVal numEmpl As Integer) Dim mySheetName As String Dim mySheet As Worksheet Dim myRange As Range Dim numOfWeeks As Integer Dim numOfShifts As Integer Dim baseCell As Range Dim cellFormula As String Dim initials As String 'Initialize variables mySheetName = Worksheets("ROSTER").Range("E7").Value Set mySheet = Worksheets(mySheetName) numOfWeeks = Worksheets("Roster").Range("E10").Value numOfShifts = numOfWeeks * 17 'Get to our starting point mySheet.Select Set myRange = Range("B5") *** myRange.Select If anyone can help, I would greatly appreciate it. . -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Could not load objects | Excel Discussion (Misc queries) | |||
OLE Objects | Excel Discussion (Misc queries) | |||
using the address properties from range objects in a sum formula | Excel Discussion (Misc queries) | |||
Embedded Objects | Excel Discussion (Misc queries) | |||
VBA Objects | Excel Discussion (Misc queries) |