Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Easier Method
I have the following macro that is assigned to a button on the formatting bar.
Note: The macro is in a different file than the file where the cells are selected. Sub SumSelectedCells() Application.ScreenUpdating = False Dim wb As Workbook Set wb = ActiveWorkbook wb.Activate myVar = InputBox("Enter Cell Location", "Create Variable") Range(myVar).Value = Application.WorksheetFunction.Sum(Application.Sele ction) Windows("zCalc.xls").Close (0) Application.ScreenUpdating = True End Sub So what this does is if I have cells highlighted in a file and I want to get the sum in a cell I click on the button to run this macro. The only thing is I have to input a cell location for the macro to put the value. How can I do this without having to input a cell address. The ideal would be : I highlight the cells ; click to run the macro ; then click in a cell and paste .... or something like that . Thank you , Steven |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Easier Method
Hi Steven,
Try something like: '========== Public SumSelectedCells() Dim mySum As Double Dim Rng As Range mySum = Application.Sum(Selection) On Error Resume Next Set Rng = Application.InputBox( _ Prompt:="Selectation cell", _ Title:="Sum Cell", _ Type:=8) On Error GoTo 0 If Not Rng Is Nothing Then Rng.Cells(1).Value = mySum End If End Sub '<<========== --- Regards. Norman "Steven" wrote in message ... I have the following macro that is assigned to a button on the formatting bar. Note: The macro is in a different file than the file where the cells are selected. Sub SumSelectedCells() Application.ScreenUpdating = False Dim wb As Workbook Set wb = ActiveWorkbook wb.Activate myVar = InputBox("Enter Cell Location", "Create Variable") Range(myVar).Value = Application.WorksheetFunction.Sum(Application.Sele ction) Windows("zCalc.xls").Close (0) Application.ScreenUpdating = True End Sub So what this does is if I have cells highlighted in a file and I want to get the sum in a cell I click on the button to run this macro. The only thing is I have to input a cell location for the macro to put the value. How can I do this without having to input a cell address. The ideal would be : I highlight the cells ; click to run the macro ; then click in a cell and paste ... or something like that . Thank you , Steven |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Easier Method
Hi Steven,
There is a sdignificant typo in my suggested code. Public SumSelectedCells() Should read: Public Sub SumSelectedCells() --- Regards. Norman |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
easier method for inserting object in cell | Excel Discussion (Misc queries) | |||
Please post this thread a correct full method, method about | New Users to Excel | |||
Please post this thread a complete correct method, method about te | New Users to Excel | |||
GetObject method not work after Call Shell Method | Excel Programming | |||
There has to be any easier way!! | New Users to Excel |