Macros

[NSNR]

I have created a macro in Personal.xls file and have assigned ctrl+A as the
key.

1. Now when I open excel with a blank worksheet and try to activate the
above
macro with the assigned key the cursor just blinks and the worksheet
refreshes.
2. My requirement is whenever I call this macro upon opening any excel file
or
worksheet, as designed in the macro, it should prompt for some user
inputs and
based on the conditional statements in the macro, it should calculate
and display
the results in the active blank worksheet from which I have activated
the macro.

How to incorporate using active worksheets, active cell, active workbook
and switching between active worksheet and the worksheet containing the
macros.

Below is the macro which works fine in the current worksheet(Personal.xls)

Sub Macro1()
Dim jjj6, jj6, jj8 As Range
Dim dp As Integer

Dim wkbktodo As Workbook
Dim ws As Worksheet
Set wkbktodo = ActiveWorkbook

'Set jjj6 = Cells(e1.k7)
'Set jjj7 = Cells(10, 7)
'Set jjj8 = Cells(11, 3)

Lengthtobeinput = InputBox("Enter Length", "Input", 1)
Breadthtobeinput = InputBox("Enter Breadth", "Input", 1)
Heighttobeinput = InputBox("Enter Height", "Input", 1)

Cells(5, 3) = Lengthtobeinput
Cells(7, 3) = Breadthtobeinput
Cells(9, 3) = Heighttobeinput

Cells(7, 5) = (Lengthtobeinput * Breadthtobeinput * Heighttobeinput) /
(1000000000)
'Cells(7, 5) = Cells(5, 3) * Cells(7, 3) * Cells(9, 3)


'ActiveCell.Offset(5, 4).Select
'dp = Cells(7, 3).Value

If Cells(7, 3).Value = Cells(24, 3).Value Then
Cells(7, 7) = Cells(7, 5).Value * Cells(24, 5).Value
Cells(7, 8) = Cells(7, 5).Value * 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

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
ActiveWorkbook.ActiveSheet = Range("E1:K7").Value

End Sub



Any suggestions are gladly appreciated.

 

[John Bundy]

This should work when you call the macro, but i suspect you won't be able to
call it with ctl-A in any workbook. You may be able to tie it to a custom
toolbar button but honestly i don't have a lot of experience with how Excel
stores and uses Hotkeys. Application level events may be of some help.
http://www.cpearson.com/excel/AppEvent.aspx