How to activate a worksheet when a macro is running in the sheet m

[Guest]

Problem:
I want to move to cell A1 (sheet 1) when a certain nr occurs in cell B1, for
ex 1. Cell B1 contains a formula that refers to a cell in sheet 2. My problem
is that this doesn´t happen until I do anything in sheet 1 like use one arrow
or klick with the mouse in a cell.

How can I activate sheet 1 within the macro below?

The macro looks like this:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Select Case Range("B1")
Case Is = 1 "The macro feel nr 1 but do not execute
until anything
Range("A1").Select happens in sheet 1"
End Select

Hope for a swift answer
BFSWE
End Sub

 

[Bob Phillips]

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Address = "$B$1" Then
If Target.Value = 1 Then
Worksheets("Sheet1").Activate
Range("A1").Select
End If
End If
End Sub


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Dave Peterson]

A slight typo:

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Address = "$B$1" Then
If Target.Value = 1 Then
With Worksheets("Sheet1")
.Activate
.Range("A1").Select
End With
End If
End If
End Sub

 

[Guest]

Hi Dave.

It still doesn´t work. Maybe the problem is how my Excel is configurated?
Maybe you can help med by doing a little experiment. If you have 2 sheets in
a workbook. In sheet1, cell B1, put a formula that fetch a nr from sheet2,
cell B1. Put your macro in the sheet1 module. Now, go to sheet2 and write 1
in cell B1. Go to sheet1 and se if excel has gone to cell A1. In my case this
doesn´t happen until I go to cell B1. I want this to happen directly without
having to do anything in the worksheet.

I hope you can help me with this problem.

Regards Bo

"Dave Peterson" skrev:

 

[Guest]

Hi Bob.

It still doesn´t work. Maybe the problem is how my Excel is configurated?
Maybe you can help med by doing a little experiment. If you have 2 sheets in
a workbook. In sheet1, cell B1, put a formula that fetch a nr from sheet2,
cell B1. Put your macro in the sheet1 module. Now, go to sheet2 and write 1
in cell B1. Go to sheet1 and se if excel has gone to cell A1. In my case this
doesn´t happen until I go to cell B1. I want this to happen directly without
having to do anything in the worksheet.

I hope you can help me with this problem.

Regards Bo

"Bob Phillips" skrev:

 

[Bob Phillips]

That is different. For one thing, you are now trying it with a referenced
cell, and another is that you are trying to do it with a Change, whereas
your original code showed SelectionChange. If you aren't selecting, you
cannot react to it.

What exactly are you trying to do, and why?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Guest]

Hi Bob.

Thanks for your answer.

In a sheet I have a clock that is counting down to zero. I want to start a
macro when the clock has reached zero. I have a macro that does this but the
problem is that I have to do something, in the sheet, for this macro to work.
In the example below I have to go to cell B1 to activate the macro even if
I´m already standing on the cell when I´m putting 1 in cell B1 in sheet2. I
hope you understand my problem.

Regards Bo