Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to activate a worksheet when a macro is running in the sheet m
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to activate a worksheet when a macro is running in the sheet m
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) "BFSWE" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to activate a worksheet when a macro is running in the sheet m
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 Bob Phillips wrote: 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) "BFSWE" wrote in message ... 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 -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to activate a worksheet when a macro is running in the she
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: 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 Bob Phillips wrote: 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) "BFSWE" wrote in message ... 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 -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to activate a worksheet when a macro is running in the she
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: 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) "BFSWE" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to activate a worksheet when a macro is running in the she
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) "BFSWE" wrote in message ... 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: 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) "BFSWE" wrote in message ... 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to activate a worksheet when a macro is running in the she
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 "Bob Phillips" wrote: 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) "BFSWE" wrote in message ... 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: 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) "BFSWE" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Worksheet- activate macro | Excel Discussion (Misc queries) | |||
Prevent code in "Sheet Activate" from running when sheet made visible from other macr | Excel Programming | |||
Return to Current Sheet in On (sheet activate) event macro | Excel Programming | |||
Activate Macro on Activating a sheet | Excel Programming | |||
Run Macro on Worksheet Activate | Excel Programming |