how read value from last selected cell? It is possible? how get adress last selected cell?

for example.

I write to cell A11 value "hello",
and next:
IF  I click "ENTER" or IF I click mouse in any different cell (A15, B10,
C1... free choice) I wont to display:
MsgBox "Value in your last selected cell = ???"

On this example MsgBox "Value in your last selected cell = hello"

it is very important for me.
I try with event of Private Sub Worksheet_Change(ByVal Target As Range) ??
but :(

thanks everybody,
Andrzej


0
a22p (10)
5/30/2005 4:56:54 PM
excel.newusers 15348 articles. 2 followers. Follow

4 Replies
456 Views

Similar Articles

[PageSpeed] 25

Andrzej

You have little choice but to use the Worksheet_Change() event as the 
Worksheet_SelectionChange() event, give you the cell being selected rather 
than the one that you have left.  You may also do better with a Public 
variable, but the Worksheet_Change event would look like this

Private Sub Worksheet_Change(ByVal Target As Range)
MsgBox "Value in the cell just changed is " & Target.Value
End Sub

-- 
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS


"Andrzej" <a22p@wp.pl> wrote in message 
news:OmEJUiTZFHA.2400@TK2MSFTNGP10.phx.gbl...
> for example.
>
> I write to cell A11 value "hello",
> and next:
> IF  I click "ENTER" or IF I click mouse in any different cell (A15, B10,
> C1... free choice) I wont to display:
> MsgBox "Value in your last selected cell = ???"
>
> On this example MsgBox "Value in your last selected cell = hello"
>
> it is very important for me.
> I try with event of Private Sub Worksheet_Change(ByVal Target As Range) ??
> but :(
>
> thanks everybody,
> Andrzej
>
> 


0
5/30/2005 5:16:43 PM
OK
it's works correctly. Thanks

but one more:
I would like that it will be works, provided that last selected cell belongs
to first column (only A)
for example last selected cell : A1, A20, A100, A40000 , etc)
Is it possible ??




U�ytkownik "Nick Hodge" <nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS> napisa� w
wiadomo�ci news:#eQcbtTZFHA.3364@TK2MSFTNGP12.phx.gbl...
> Andrzej
>
> You have little choice but to use the Worksheet_Change() event as the
> Worksheet_SelectionChange() event, give you the cell being selected rather
> than the one that you have left.  You may also do better with a Public
> variable, but the Worksheet_Change event would look like this
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> MsgBox "Value in the cell just changed is " & Target.Value
> End Sub
>
> --
> HTH
> Nick Hodge
> Microsoft MVP - Excel
> Southampton, England
> nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS
>
>
> "Andrzej" <a22p@wp.pl> wrote in message
> news:OmEJUiTZFHA.2400@TK2MSFTNGP10.phx.gbl...
> > for example.
> >
> > I write to cell A11 value "hello",
> > and next:
> > IF  I click "ENTER" or IF I click mouse in any different cell (A15, B10,
> > C1... free choice) I wont to display:
> > MsgBox "Value in your last selected cell = ???"
> >
> > On this example MsgBox "Value in your last selected cell = hello"
> >
> > it is very important for me.
> > I try with event of Private Sub Worksheet_Change(ByVal Target As Range)
??
> > but :(
> >
> > thanks everybody,
> > Andrzej
> >
> >
>
>


0
a22p (10)
5/30/2005 6:02:26 PM
Andrzej

Sure

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Columns("A:A"), Target) Is Nothing Then
MsgBox "Value in the cell just changed in column A is " & Target.Value
End If
End Sub


-- 
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS


"Andrzej" <a22p@wp.pl> wrote in message 
news:O5wv7GUZFHA.3780@tk2msftngp13.phx.gbl...
> OK
> it's works correctly. Thanks
>
> but one more:
> I would like that it will be works, provided that last selected cell 
> belongs
> to first column (only A)
> for example last selected cell : A1, A20, A100, A40000 , etc)
> Is it possible ??
>
>
>
>
> U�ytkownik "Nick Hodge" <nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS> napisa� 
> w
> wiadomo�ci news:#eQcbtTZFHA.3364@TK2MSFTNGP12.phx.gbl...
>> Andrzej
>>
>> You have little choice but to use the Worksheet_Change() event as the
>> Worksheet_SelectionChange() event, give you the cell being selected 
>> rather
>> than the one that you have left.  You may also do better with a Public
>> variable, but the Worksheet_Change event would look like this
>>
>> Private Sub Worksheet_Change(ByVal Target As Range)
>> MsgBox "Value in the cell just changed is " & Target.Value
>> End Sub
>>
>> --
>> HTH
>> Nick Hodge
>> Microsoft MVP - Excel
>> Southampton, England
>> nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS
>>
>>
>> "Andrzej" <a22p@wp.pl> wrote in message
>> news:OmEJUiTZFHA.2400@TK2MSFTNGP10.phx.gbl...
>> > for example.
>> >
>> > I write to cell A11 value "hello",
>> > and next:
>> > IF  I click "ENTER" or IF I click mouse in any different cell (A15, 
>> > B10,
>> > C1... free choice) I wont to display:
>> > MsgBox "Value in your last selected cell = ???"
>> >
>> > On this example MsgBox "Value in your last selected cell = hello"
>> >
>> > it is very important for me.
>> > I try with event of Private Sub Worksheet_Change(ByVal Target As Range)
> ??
>> > but :(
>> >
>> > thanks everybody,
>> > Andrzej
>> >
>> >
>>
>>
>
> 


0
5/30/2005 6:11:04 PM
hi Nick
I thought about this..  Thank for help

Andrzej



U�ytkownik "Nick Hodge" <nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS> napisa� w
wiadomo�ci news:e29TxLUZFHA.3096@TK2MSFTNGP15.phx.gbl...
> Andrzej
>
> Sure
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> If Not Application.Intersect(Columns("A:A"), Target) Is Nothing Then
> MsgBox "Value in the cell just changed in column A is " & Target.Value
> End If
> End Sub
>
>
> --
> HTH
> Nick Hodge
> Microsoft MVP - Excel
> Southampton, England
> nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS
>
>
> "Andrzej" <a22p@wp.pl> wrote in message
> news:O5wv7GUZFHA.3780@tk2msftngp13.phx.gbl...
> > OK
> > it's works correctly. Thanks
> >
> > but one more:
> > I would like that it will be works, provided that last selected cell
> > belongs
> > to first column (only A)
> > for example last selected cell : A1, A20, A100, A40000 , etc)
> > Is it possible ??
> >
> >
> >
> >
> > U�ytkownik "Nick Hodge" <nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS>
napisa�
> > w
> > wiadomo�ci news:#eQcbtTZFHA.3364@TK2MSFTNGP12.phx.gbl...
> >> Andrzej
> >>
> >> You have little choice but to use the Worksheet_Change() event as the
> >> Worksheet_SelectionChange() event, give you the cell being selected
> >> rather
> >> than the one that you have left.  You may also do better with a Public
> >> variable, but the Worksheet_Change event would look like this
> >>
> >> Private Sub Worksheet_Change(ByVal Target As Range)
> >> MsgBox "Value in the cell just changed is " & Target.Value
> >> End Sub
> >>
> >> --
> >> HTH
> >> Nick Hodge
> >> Microsoft MVP - Excel
> >> Southampton, England
> >> nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS
> >>
> >>
> >> "Andrzej" <a22p@wp.pl> wrote in message
> >> news:OmEJUiTZFHA.2400@TK2MSFTNGP10.phx.gbl...
> >> > for example.
> >> >
> >> > I write to cell A11 value "hello",
> >> > and next:
> >> > IF  I click "ENTER" or IF I click mouse in any different cell (A15,
> >> > B10,
> >> > C1... free choice) I wont to display:
> >> > MsgBox "Value in your last selected cell = ???"
> >> >
> >> > On this example MsgBox "Value in your last selected cell = hello"
> >> >
> >> > it is very important for me.
> >> > I try with event of Private Sub Worksheet_Change(ByVal Target As
Range)
> > ??
> >> > but :(
> >> >
> >> > thanks everybody,
> >> > Andrzej
> >> >
> >> >
> >>
> >>
> >
> >
>
>


0
a22p (10)
5/30/2005 6:28:29 PM
Reply:

Similar Artilces:

Using cell reference with logical operator in DGET expression
I am using DGET to search an array in the worksheet to find a particular percent to use in a calculation elsewhere in the spreadsheet. The row members of the array contain a series of from and to values that I use to identify which row has the percent I am looking for. I have defined the array as a range and I can use the logical operatirs with numeric values in the range criteria and everything works fine. For example, I can use <500 in the appropriate cell in range criteria to find the percent to use when the value for that column in the array is less than 500. All working f...

reference cell above even if row deleted
I would like to be able to reference a cell imediately above the current cell even if the row above that cell had just been deleted. For instance: contents of A5: =A4+$B$1 then delete row 4 and rather than have contents of A4:#REF!+$B$1 have it A4:A3+$B$1 Is there some way to do this? Thanks for any tips. John Keith kd0gd@juno.com You can use the OFFSET function: =OFFSET(A5,-1,0)+$B$1 John Keith wrote: > I would like to be able to reference a cell imediately above the > current cell even if the row above that cell had just been deleted. > > For instance: > > conte...

Getting a subform control to requery
Main Form : F_BU Subform1: F_BU_Cat2 SubForm2: F_BU_Cat3 When I click on one of the records in subform1 (field BillCat) the OnClick event uses the value in the field as a criteria in a query that is used in the combox (cbxCat3) in Subform 2. Here is the code I was trying to use: Me.Parent!F_BU_Cat3.Form!cbxCat3.Requery It was working for a while but now not when I click on the "BillCat" field in Subform 1 I get: Method: Form" of Object ' _Subform' Failed. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-form...

SumIFS Cell
I am trying to reference a cell in a formula and I would like to say anything greater then cell J2 but when I input this it searches for text. =SUMIFS($B$2:$B$27,$A$2:$A$27,"000001",$C$2:$C$27,>D2) A B C D 1 00001 100 40248 40248 2 12001 150 40237 3 15001 200 40237 4 00001 150 40290 5 00001 50 40350 I would like the total to return 200 because Cell C5 and C4 are larger than Cell D1 in respect to the Sku number I would like to sum. But when I put in >J2 it enters “>J2” and it won’t return a value other then 0. (If I put in just D2 then it returns a valu...

We are getting stat=Deferred: Connection reset errors .....
Does anyone know how to fix this issue? I am getting a lot of emails BUT an Exchange admin from another company that sends SMTP email to is saying that he sees a lot of stat=Deferred: Connection reset error messages in his log file. very very weird. Has anyone seen this issue? He can telnet to the server fine etc... plus I am getting email from his company but it is deferring aa ton though. Thanks What version of Exchange and SP level Oliver Exchange 5.5 SP 4 is our IMS. "Oliver Moazzezi" wrote: > What version of Exchange and SP level > > Oliver > >...

can I get "step by step" on color seperating for commercial printi
This is kind of an extension on my previous message but I feel it would help a lot more. Can someone give me a quick step by step on making a document with text and photo into a press quality color seperated piece using PMS colors and process black? Or maybe point me to a site that can give step by step on that stuff? ...

Table Cell
I have created a form using a table. I want to lock or block cells that should not be changed. How do you do this? ...

Letter Writing Assistant selection based on Smart List Lookup
We had a great opportunity to use the letter writing assistance to send out a notice to any vendor used within the last 6 months. Using the smartlist lookup up we can see those vendors we have issued a check to based on Last Check date, but the Letter Writing Assistant only allows you to select ranges. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the ...

Where can we get xml scheme for tuning setting of WM2003/WM5.0 PDA
Hi all, We are working upon setting up a lot of WM2003/WM5.0 empowered mobile devices according with company standards. As far as I know these settings are tuned through xml file. Then this xml file has to be deployed deployed/executed onto mobile device. My question is where xml scheme for tuning such setting is published so we may assign necessary properties referencing it? Thank you in advance. ...

Cell Protection #8
Hello: I am using the UserInterfaceOnly to protect my worksheet. I have it placed in the workbook object. Unfortunately, after the workbook is open the user can Tools>>Protection>>Unprotect Sheet. Is there a way to password out this option? I would like the user to have access to only the standard toolbar, is there a method to stop them from enabling various toolbars, menus options and manipulating the sheet, etc. Kind regards, D.Parker Wouldn't it be simpler to use a password that the user doesn't know? And don't forget to protect your project. In th...

Sorting in select query. manually
Hello, I have a select query and I would like to sort on the field month. but I want to be able to specify the order myself (not descending nor ascending). how can I do this? thank you! You create a table with the data you which you would see in the Month field and the sort order that you want. You then join these two tables in the query and sort on it. Something like this: Month MonthSort Jan 12 Feb 11 Mar 3 and so on. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycl...

Excel & Word auto selection from menus
Ok, I'm a keyboarder, not a mouser. I use quick commands like the letter code or arrows on menus to speed along without the grab, aquire, click business. Excel & Word seem to be periodically (not always) automatically selecting menu items based on prior selections if I use arrow selectors. How do I turn this off? I get no help from "Help". Please reply to my e-mail address: legreenwood@hotmail.com. Thanks! Would love to get rid of this nuisance & keep speeding along. ...

How to get Global Notification of browser request...
Hye, I am trying to get notification of any browser request... (not only that of the local server.. so filters will not be the right way to solve problem) So, whenever any request like (http://www.yahoo.com/) is made in IE, I want my DLL routine or my EXE should be called.. So, how to do that, any suggestion will be highly appreciated... -- Jigar Mehta jbmehtain@yahoo.co.in the program you need is called Browser Helper Objects http://msdn.microsoft.com/library/en-us/dnwebgen/html/bho.asp -- Command what is yours Conquer what is not Microsoft Most Valuable Professional [VC++] h...

How do you move the curser around in a cell
What do mean by "cursor" and what do you want to do? HTH Otto "krisf" <krisf@discussions.microsoft.com> wrote in message news:8BD5F197-4745-4FD3-95BE-80A10897020F@microsoft.com... > ...

Clause order in SQL select query
Are there any rules that govern clause order? Does the parser care? Obviously SELECT must be first but does WHERE have to precede ORDER BY? This question arises because I have a situation where the VBA code would be easier if the WHERE could be the final clause. That way I could have an unchanging SELECT, FROM, ORDER BY string and just tag the WHERE; on the tail. My thinking then turned to whether there is a mandated order for other clauses too. Is there an article somewhere to explain it? -- Len ______________________________________________________ remove nothing for v...

select query of parent and child in one row
I have a parent table and a child table one parent has between 1 and 6 children in the child table I wont a query to have the parent and the child in one "row" how do I create that? thanks for helping me Your table structure is not correct for a relational database but this will get you there. This has three but you get the idea and expand on it. Place the table in the design grid as many times as you have field to combine. SELECT MultipleFieldData.x, MultipleFieldData.a & IIf(MultipleFieldData_1.b Is Null,Null," " & MultipleFieldData_1.b) & IIf(MultipleF...

Emails getting stuck in Outbox
Hi I am sending a number of emails (around 600) using the vb.net code below. The problem is that the emails end up in the OL2007 Outbox with a clock icon and do not go out. If I open one of the email items and click Send then that item goes out but this technique is too cumbersome for 600 or so emails. What is the problem and how can I fix it? Many Thanks Regards Dim objOutlook As Object = CreateObject("Outlook.Application") Dim NS As Object = objOutlook.GetNamespace("MAPI") Dim objOutlookMsg As Object Dim BodyText As String ...

Flag a value in a group...
Hi all, tia for any insight. I have the following query that gives some totals for qty's in a releases table, pulling and grouping information (part number, rev and desc) from a details table. Table heirarchy is (one to manys) tblOrders -> tblOrderDetails -> tblOrderReleases SELECT tblOrderDetails.fldPart, tblOrderDetails.fldRev, tblOrderDetails.fldDescription, Min(tblOrderReleases.fldDueDate) AS cfldFirstDue, Sum([tblOrderReleases].[fldQty])-Sum([tblOrderReleases].[fldQtyToBE]) AS cfldQtyToProcess FROM tblOrders LEFT JOIN (tblOrderDetails LE...

mass definitions of cell names
Ok, I have a problem. I have to name a large number of cells in different sheets. I'm hoping that there is a way in a macro, or some other way to define these in an easier way other than one by one. Here is an example: 1 2 3 4 1001 --------- --------- --------- --------- 1002 --------- --------- --------- --------- 1209 --------- --------- --------- --------- 1210 --------- --------- --------- --------- This would be the column and row headers for the cells. Below is how the...

Moving a line of data when information is entered in a specific cell.
HELP NEEDED. I am trying to create a spreadsheet to keep track of problems that we encounter at our volunteer organization. It is pretty simple. I am having one problem. One of my fields is "completion information." What I need is when completion information is entered into the field, I want that line of information to move to an "archived sheet". In effect, so that I have a sheet with only active problems and when the completion information is entered it moves to the archived sheet. Anyone have any suggestions? -- opshmo -----------------------------------------------...

Reading UUENCODE
For some reason when a client is sending UUENCODE type messages to my Exchange Server the Clients can not ready the Email or the Attachments. All Attachments are arriving with the same size and when opened display as a X. Has anybody seen this problem before ...

How Do I specify where XML data gets imported into Excel
I have XML data that I would like to import up as fllows in a spreasheet: Cell A2: Job Number Cell A3: Customer Number A4 through A29 should recive the <AMOUNT> data B4 through B29 should receive the <QUANTITY> data etc etc Sometimes all the fields will be filled, sometimes not. Any help would be greatly appreciated. Regards, Diane Hi Diane! You should see therre: microsoft.public.xml Starwing ...

Set a default value to Drop Down Box
Is it possible to set a 'default' value to a drop down box, so that n matter what value was selected when it was saved, when the workboo opens it changes to the default value? I have a written a macro to do this for all the input cells that ar not drop downs, but cannot figure out a way to change values in dro down forms. Thanks Bria -- Message posted from http://www.ExcelForum.com What's the default value? The first on the list? If yes: Option Explicit Sub auto_open() Dim wks As Worksheet Dim myDD As DropDown For Each wks In ThisWorkbook.Worksheets ...

Calculate per minute cost of cell phone usage
I have a spreadsheet that I=92m trying to use to calculate the cost per minute of cellular calls. I have 3 plans (in rows 3, 4, and 5) that each provide a monthly cost (column a), and the number of minutes included in that cost (column b). There is also a cost per minute if you go over that allowance (cell E2). I have a field (cell C7) that I use to enter the average number of minutes used. I have a drop down list (cell C8) that allows you to select the plan you have. I have a formula that is currently written as: =3DSUM(((C7-C8)*E2)+A5)/C7 where: C7 =3D average number of ...

Value pack?
Hi all, I'm looking for the Office X value pack with the german/french dictionaries and so on, I seem to have deleted mine by mistake. Could someone maybe mail it to me? Thanks :) In article <BB81381D.6387%n.prisi@b3design.ch>, "? Nicholas.Prisi ?" <n.prisi@b3design.ch> wrote: > Hi all, > > I'm looking for the Office X value pack with the german/french dictionaries > and so on, I seem to have deleted mine by mistake. Could someone maybe mail > it to me? > The Value Pack is a folder on your install CD. Yeah, unfortunatly I lost my cd whil...