Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
CALLING MACROS FROM A NEW WORKSHEET
I have created a macro which accepts length, breadth & height of a cubicle
through user input prompts. based on a certain condition based calculations it calculates the volume and cost of fabricating the cubicle and displays in the worksheet in which it is created. Now I want to use this macro to be used while working for projects in different worksheets, so that the user enters the input data at the prompt and the result of the macro is displayed in the active worksheet of the current project rather than the worksheet in which it was created. I am struck up in this, due to which various other macros have to be developed based on this method. -- reply to my posts are welcome |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
CALLING MACROS FROM A NEW WORKSHEET
Thu, 31 Jan 2008 01:22:00 -0800 from NSNR :
I have created a macro ... Now I want to use this macro to be used while working for projects in different worksheets, http://www.rondebruin.nl/personal.htm says "If you want that certain code is available in all your workbooks, then use your PERSONAL.XLS or in Excel 2007 your PERSONAL.XLSB file." and explains how to do it. -- Stan Brown, Oak Road Systems, Tompkins County, New York, USA http://OakRoadSystems.com/ "If there's one thing I know, it's men. I ought to: it's been my life work." -- Marie Dressler, in /Dinner at Eight/ |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
CALLING MACROS FROM A NEW WORKSHEET
I mean to say that I have created thsi macro and stored in Personal.xls
worksheet and stored under C:\programs files\MsOffice\Office10\XL Start folder. Also I have assigned "Ctrl+B" as the shortcut key to use this macro in other worksheets. During such instance I want the result of the macro displayed in a cell of this new macro. solution to the problem expected soon -- reply to my posts are welcome "Stan Brown" wrote: Thu, 31 Jan 2008 01:22:00 -0800 from NSNR : I have created a macro ... Now I want to use this macro to be used while working for projects in different worksheets, http://www.rondebruin.nl/personal.htm says "If you want that certain code is available in all your workbooks, then use your PERSONAL.XLS or in Excel 2007 your PERSONAL.XLSB file." and explains how to do it. -- Stan Brown, Oak Road Systems, Tompkins County, New York, USA http://OakRoadSystems.com/ "If there's one thing I know, it's men. I ought to: it's been my life work." -- Marie Dressler, in /Dinner at Eight/ |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
CALLING MACROS FROM A NEW WORKSHEET
In your macro that you place into Personal.xls do not hard-code workbook,
worksheet or range references. Use ActiveWorkbook, ActiveSheet, ActiveCell Post your code if you can't figure this out. Gord Dibben MS Excel MVP On Thu, 31 Jan 2008 02:48:00 -0800, NSNR wrote: I mean to say that I have created thsi macro and stored in Personal.xls worksheet and stored under C:\programs files\MsOffice\Office10\XL Start folder. Also I have assigned "Ctrl+B" as the shortcut key to use this macro in other worksheets. During such instance I want the result of the macro displayed in a cell of this new macro. solution to the problem expected soon |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
CALLING MACROS FROM A NEW WORKSHEET
The code I have created for the macro is as follows:
Sub cubicle Dim jjj6, jj6, jj8 As Range Dim dp As Integer 'Set jjj6 = ActiveCell.Offset(1, 0) 'Set jjj7 = ActiveCell.Offset(3, 0) 'Set jjj8 = Cells(21, 8) ' the input prompt shall appear whenver the macro is invoked in any worksheet. Lengthtobeinput = InputBox("Enter Length", "Input", 1) Breadthtobeinput = InputBox("Enter Breadth", "Input", 1) Heighttobeinput = InputBox("Enter Height", "Input", 1) 'this calculates the volume and this should be displayed in the active worksheet from where the macro is invoked. jjj6 = (Lengthtobeinput * Breadthtobeinput * Heighttobeinput) / (1000000000) 'Cells(7, 5) = Cells(5, 3) * Cells(7, 3) * Cells(9, 3) ' based on the conditional data which has been stored in the worksheet in which the macro is created, the following checks are made. If Breadthtobeinput = Cells(24, 3).Value Then jj7 = (Lengthtobeinput * Breadthtobeinput * Heighttobeinput) / (1000000000) * (Cells(24, 5).Value) jj8 = jj6 * Cells(24, 7).Value 'Cells(7, 10) = Cells(7, 5).Value * Cells(24, 9).Value 'Cells(7, 11) = Cells(7, 5).Value * Cells(24, 11).Value ' the above 4 results jj7, jj8, cells(7,10) and cells(7,11) must be displayed in a designated \ specified cell of the active worksheet. Else If Cells(7, 3).Value = Cells(26, 3).Value Then Cells(7, 7) = Cells(7, 5).Value * Cells(26, 5).Value Cells(7, 8) = Cells(7, 5).Value * Cells(26, 7).Value Cells(7, 10) = Cells(7, 5).Value * Cells(26, 9).Value Cells(7, 11) = Cells(7, 5).Value * Cells(26, 11).Value Else If Cells(7, 3).Value = Cells(28, 3).Value Then Cells(7, 7) = Cells(7, 5).Value * Cells(28, 5).Value Cells(7, 8) = Cells(7, 5).Value * Cells(28, 7).Value Cells(7, 10) = Cells(7, 5).Value * Cells(28, 9).Value Cells(7, 11) = Cells(7, 5).Value * Cells(28, 11).Value Else If Cells(7, 3).Value = Cells(30, 3).Value Then Cells(7, 7) = Cells(7, 5).Value * Cells(28, 5).Value Cells(7, 8) = Cells(7, 5).Value * Cells(28, 7).Value Cells(7, 10) = Cells(7, 5).Value * Cells(28, 9).Value Cells(7, 11) = Cells(7, 5).Value * Cells(28, 11).Value Else If Cells(7, 3).Value = Cells(32, 3).Value Then Cells(7, 7) = Cells(7, 5).Value * Cells(32, 5).Value Cells(7, 8) = Cells(7, 5).Value * Cells(32, 7).Value Cells(7, 10) = Cells(7, 5).Value * Cells(32, 9).Value Cells(7, 11) = Cells(7, 5).Value * Cells(32, 11).Value Else End If End If End If End If End If End Sub -- help sought in rectifying the above macro. reply to my posts are welcome "Gord Dibben" wrote: In your macro that you place into Personal.xls do not hard-code workbook, worksheet or range references. Use ActiveWorkbook, ActiveSheet, ActiveCell Post your code if you can't figure this out. Gord Dibben MS Excel MVP On Thu, 31 Jan 2008 02:48:00 -0800, NSNR wrote: I mean to say that I have created thsi macro and stored in Personal.xls worksheet and stored under C:\programs files\MsOffice\Office10\XL Start folder. Also I have assigned "Ctrl+B" as the shortcut key to use this macro in other worksheets. During such instance I want the result of the macro displayed in a cell of this new macro. solution to the problem expected soon |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calling macros in diff worksheet | Excel Discussion (Misc queries) | |||
calling macros from worksheet to another | Excel Discussion (Misc queries) | |||
calling up information from a different worksheet. | Excel Worksheet Functions | |||
Calling macros in another workbook | Excel Worksheet Functions | |||
calling a series macros with a marco | Excel Discussion (Misc queries) |