selecting sheets within a macro
 

I would like to be able to click on the sheet I want within a macro and then
continue on with the macro code

for example this code chooses all the sheet within the test workbook and
puts a 0 in A1

Windows("test.xls").Activate
Sheets.Select
Range("A1").Select
ActiveCell.FormulaR1C1 = "0"
Range("A2").Select

instead I want the workbook to pop up so that I can click on a single sheet
I select
and put a 0 in only that single sheet

selecting sheets within a macro
 

Check this out:

Sub rangerover()
Dim r As Range
On Error Resume Next
Set r = Nothing
Set r = Application.InputBox("Select a cell with the mouse", Type:=8)
MsgBox ("Address is: " & r.Address)
MsgBox ("Worksheet is: " & r.Parent.Name)
MsgBox ("Workbook is: " & r.Parent.Parent.Name)
r.Parent.Parent.Activate
r.Parent.Activate
r.Select
r.Value = 0
End Sub

The code stops and asks the user to click any cell in any sheet of any open
workbook. The code the describes the cell, goes to the cell and puts zero in
that cell.
--
Gary''s Student
gsnu200710


"ale" wrote:

I would like to be able to click on the sheet I want within a macro and then
continue on with the macro code

for example this code chooses all the sheet within the test workbook and
puts a 0 in A1

Windows("test.xls").Activate
Sheets.Select
Range("A1").Select
ActiveCell.FormulaR1C1 = "0"
Range("A2").Select

instead I want the workbook to pop up so that I can click on a single sheet
I select
and put a 0 in only that single sheet

selecting sheets within a macro
 

Thanks a bunch!! I think this will do the trick. I tested it out and it
seems to work well. I'm writing a bigger macro so I'll repost if I need more
help. I was hoping the answer was something simple like adding something to
sheets(...) but I guess not.

"Gary''s Student" wrote:

Check this out:

Sub rangerover()
Dim r As Range
On Error Resume Next
Set r = Nothing
Set r = Application.InputBox("Select a cell with the mouse", Type:=8)
MsgBox ("Address is: " & r.Address)
MsgBox ("Worksheet is: " & r.Parent.Name)
MsgBox ("Workbook is: " & r.Parent.Parent.Name)
r.Parent.Parent.Activate
r.Parent.Activate
r.Select
r.Value = 0
End Sub

The code stops and asks the user to click any cell in any sheet of any open
workbook. The code the describes the cell, goes to the cell and puts zero in
that cell.
--
Gary''s Student
gsnu200710