CALLING MACROS FROM A NEW WORKSHEET

[NSNR]

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.

 

[Stan Brown]

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.

 

[NSNR]

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

 

[Gord Dibben]

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

 

[NSNR]

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