Registrating data on a sheet without activating it

J

johan

Hello,

Simple (?) question.
How can I set a value or a text in a specific cell on a specific sheet
without activating that sheet.
For example,.....

My active sheet is sheetname "Start" and when I activate sheetname
"Sheet2" I want registrate the text "Sheet2" in cell A1 of sheetname
"Start".

I had used :

Private Sub Worksheet_Activate() ' -> activatemacro on sheet2
Sheets("START").Select
Range("A1").Select
Selection.ClearContents
ActiveCell.FormulaR1C1 = "Sheet2"
Sheets("Sheet2").Select
Range("A1").Select
End Sub

Problem now is that this a loop. I think (?) the solution is
registration on sheet "Start" without activating it, otherwise you
activating Sheet2 again... and again...

regards,
Johan.
 
J

Joerg

Yes, it's a loop. Try:

Private Sub Worksheet_Activate() ' -> activatemacro on sheet2
Worksheets("START").Range("A1") = "START"
End Sub

Cheers,

Joerg Mochikun
 
J

Joerg

Sorry, did get the input text wrong, so line should read:
Worksheets("START").Range("A1") = "Sheet2"

Joerg
 
N

NickHK

Johan,
Worksheets("Start").Range("A1").Value="Sheet2"

or possibly, depending on what you are doing:
Worksheets("Start").Range("A1").Value=ActiveSheet.Name

NickHK
 
J

johan

Thanks, works fine.

Another supplemental question....

Now the last used sheet (with the above mentioned registrationmacro)
is registrated in sheet "Start" cell A1.
Question...
How to open on a different sheet, without the above mentioned
registrationmacro, the sheetname that is registrated in sheet "Start"
cell A1.

For example....
In sheet Start cell A1 last used sheetname "Sheet2" is registrated.
Now I'm in sheet 10 and with a macro I want to open the sheetname that
is registrated in sheet "Start" cell A1.

thanks a lot,
regards.... Johan
 
N

NickHK

Not sure what you are doing, but something like:

Dim SheetName As String
SheetName = Worksheets("Start").Range("A1").Value
Worksheets(SheetName).Activate

NickHK
 
J

Joerg

I assume that you have more than just Sheet2 to put its name into A1 of
START. In this case you shouldn't use macros in each sheet. I think it would
be better to put something like

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
If Sh.Name <> "START" Then Worksheets("START").Range("A1") = Sh.Name
End Sub

into the code section of ThisWorkbook. This will put the sheetname of
whatever sheet is activated (except the START sheet itself) into A1 of
START. NickHK already showed you how to use the resulting text for your
code.

Cheers,

Joerg
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top