How to activate an open worksheet?

A

absolutfreedom

Hi, I have a quick question: How to bring a non-active excel sheet o
top? (i.e. make it the active one)?

Now let's say I open 2 excel files (A.xls and B.xls). Now, whenever
received an input, I have a formula which will be triggered by an OnKe
"~". That formula/module will check whether that input belong to A.xl
or B.xls. So if I enter an input into A.xls and it does belong t
A.xls, nothing happen. However, if I enter an input to A.xls, an
actually that input belong to B.xls, I have to delete that input fro
A.xls, and fill it to B.xls.
I got everything working, but I can't bring B.xls on top/active. S
what my program does right now, it only fill B.xls (which is still o
the back). How to make it on top/activate it?
I used this:
Workbooks("B.xls").Activate
Workbooks("B.xls").Sheets(1).Range("C" & Line).Select
ActiveCell.Value = inputvalue

Any suggestion? Oh yeah, and also when I try to SendKey "~", it sen
the Enter key to A.xls. Actually I want it to be sent to B.xls.

Thanks

Adria
 
J

Jim Rech

Your post is really unclear I'm afraid...

What does it mean to "receive an input"?

Why are you using OnKey? It should only be used if it's absolutely
necessary and it is rarely absolutely necessary.

Fyi, you do not have to activate a sheet/cell to make an entry in it:

Workbooks("B.xls").Sheets(1).Range("C" & Line).Value = inputvalue

will work if this worksheet is active or inactive or hidden...

--
Jim Rech
Excel MVP
message | Hi, I have a quick question: How to bring a non-active excel sheet on
| top? (i.e. make it the active one)?
|
| Now let's say I open 2 excel files (A.xls and B.xls). Now, whenever I
| received an input, I have a formula which will be triggered by an OnKey
| "~". That formula/module will check whether that input belong to A.xls
| or B.xls. So if I enter an input into A.xls and it does belong to
| A.xls, nothing happen. However, if I enter an input to A.xls, and
| actually that input belong to B.xls, I have to delete that input from
| A.xls, and fill it to B.xls.
| I got everything working, but I can't bring B.xls on top/active. So
| what my program does right now, it only fill B.xls (which is still on
| the back). How to make it on top/activate it?
| I used this:
| Workbooks("B.xls").Activate
| Workbooks("B.xls").Sheets(1).Range("C" & Line).Select
| ActiveCell.Value = inputvalue
|
| Any suggestion? Oh yeah, and also when I try to SendKey "~", it send
| the Enter key to A.xls. Actually I want it to be sent to B.xls.
|
| Thanks
|
| Adrian
|
|
| ---
|
|
 
A

absolutfreedom

Hi Jim:
My bad, actually I gotta say that whenever I input a value (manually
into any one of the sheet, and then press "Enter" key, ithe sheet wil
trigger a subroutine which will check whether this input belong to thi
sheet or the other one (which is inactive).

Yeap, I can input the value while the sheet is inactive, but then
gotta make that particular sheet to be on top, so that the user i
aware that the "input" belong to that sheet.
I tried to do: Workbooks("B.xls").Activate, but it still won't brin
the B.xls to be on top.

Do you have any suggestion? Thanks!

Adria
 
D

DNF Karran

Is the sub being triggered while you are still in the cell? This wil
prevent the other workbook being activated
 
J

Jim Rech

What you want to do is pretty doable but, as I said, you shouldn't use OnKey
unless you have no other choice. You have other choices here. I can send
you an example if you give me your email address.

--
Jim Rech
Excel MVP

message | Hi Jim:
|
| >>Now, whenever I received an input
| My bad, actually I gotta say that whenever I input a value (manually)
| into any one of the sheet, and then press "Enter" key, ithe sheet will
| trigger a subroutine which will check whether this input belong to this
| sheet or the other one (which is inactive).
|
| Yeap, I can input the value while the sheet is inactive, but then I
| gotta make that particular sheet to be on top, so that the user is
| aware that the "input" belong to that sheet.
| I tried to do: Workbooks("B.xls").Activate, but it still won't bring
| the B.xls to be on top.
|
| Do you have any suggestion? Thanks!
|
| Adrian
|
|
| ---
|
|
 
A

absolutfreedom

DNF Karran: the sub is triggered once we press "Enter" key afte
inputing the value. Technically, the cell is not in the Edit-mode.

Jim Rech: Here's my email: (e-mail address removed)

Thanks!

Adria
 
J

Jim Rech

Example sent.

--
Jim Rech
Excel MVP
message | DNF Karran: the sub is triggered once we press "Enter" key after
| inputing the value. Technically, the cell is not in the Edit-mode.
|
| Jim Rech: Here's my email: (e-mail address removed)
|
| Thanks!
|
| Adrian
|
|
| ---
|
|
 

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