Registrating data on a sheet without activating it

[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.

 

[Joerg]

Yes, it's a loop. Try:

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

Cheers,

Joerg Mochikun

 

[Joerg]

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

Joerg

 

[NickHK]

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

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

NickHK

 

[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

 

[NickHK]

Not sure what you are doing, but something like:

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

NickHK

 

[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