A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

Use DocProps in a change declaration



 
 
Thread Tools Display Modes
  #1  
Old January 20th 05, 10:53 PM
chris w
external usenet poster
 
Posts: n/a
Default Use DocProps in a change declaration

Below is a change declaration I have in a sheet, is it possible to have a
similar change declaration using docProps in another cell? So when the
change is made to cell "I7" the date is put in "J7" and the current author is
put in "L7"

I tried to copy what I have below except change = Now to =
DocProps("author") and that didnt work.

Private Sub Worksheet_change(ByVal Target As Range)

If Not Intersect(Target, Range("I7")) Is Nothing Then
Range("J7") = Now
End If

End Sub
Ads
  #2  
Old January 21st 05, 12:17 AM
Bob Phillips
external usenet poster
 
Posts: n/a
Default

Private Sub Worksheet_change(ByVal Target As Range)

If Not Intersect(Target, Range("I7")) Is Nothing Then
Range("J7") = Now
Range("L7").Value = ActiveWorkbook.BuiltinDocumentProperties("author")
End If

End Sub

--

HTH

RP
(remove nothere from the email address if mailing direct)


"chris w" > wrote in message
...
> Below is a change declaration I have in a sheet, is it possible to have a
> similar change declaration using docProps in another cell? So when the
> change is made to cell "I7" the date is put in "J7" and the current author

is
> put in "L7"
>
> I tried to copy what I have below except change = Now to =
> DocProps("author") and that didnt work.
>
> Private Sub Worksheet_change(ByVal Target As Range)
>
> If Not Intersect(Target, Range("I7")) Is Nothing Then
> Range("J7") = Now
> End If
>
> End Sub



  #3  
Old January 21st 05, 03:23 AM
chris w
external usenet poster
 
Posts: n/a
Default

Worked like a charm, thanx

Where can I find a "dictionary" of VB code or the pop up entries that come
up to help complete the code?

"Bob Phillips" wrote:

> Private Sub Worksheet_change(ByVal Target As Range)
>
> If Not Intersect(Target, Range("I7")) Is Nothing Then
> Range("J7") = Now
> Range("L7").Value = ActiveWorkbook.BuiltinDocumentProperties("author")
> End If
>
> End Sub
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "chris w" > wrote in message
> ...
> > Below is a change declaration I have in a sheet, is it possible to have a
> > similar change declaration using docProps in another cell? So when the
> > change is made to cell "I7" the date is put in "J7" and the current author

> is
> > put in "L7"
> >
> > I tried to copy what I have below except change = Now to =
> > DocProps("author") and that didnt work.
> >
> > Private Sub Worksheet_change(ByVal Target As Range)
> >
> > If Not Intersect(Target, Range("I7")) Is Nothing Then
> > Range("J7") = Now
> > End If
> >
> > End Sub

>
>
>

  #4  
Old January 21st 05, 12:58 PM
Bob Phillips
external usenet poster
 
Posts: n/a
Default

Chris,

This is the answer I gave to a similar question this morning

http://msdn.microsoft.com/library/de...texcel2000.asp

The object browser in VBA

The VBA Help file (look up Excel Object Model)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"chris w" > wrote in message
news
> Worked like a charm, thanx
>
> Where can I find a "dictionary" of VB code or the pop up entries that come
> up to help complete the code?
>
> "Bob Phillips" wrote:
>
> > Private Sub Worksheet_change(ByVal Target As Range)
> >
> > If Not Intersect(Target, Range("I7")) Is Nothing Then
> > Range("J7") = Now
> > Range("L7").Value =

ActiveWorkbook.BuiltinDocumentProperties("author")
> > End If
> >
> > End Sub
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "chris w" > wrote in message
> > ...
> > > Below is a change declaration I have in a sheet, is it possible to

have a
> > > similar change declaration using docProps in another cell? So when

the
> > > change is made to cell "I7" the date is put in "J7" and the current

author
> > is
> > > put in "L7"
> > >
> > > I tried to copy what I have below except change = Now to =
> > > DocProps("author") and that didnt work.
> > >
> > > Private Sub Worksheet_change(ByVal Target As Range)
> > >
> > > If Not Intersect(Target, Range("I7")) Is Nothing Then
> > > Range("J7") = Now
> > > End If
> > >
> > > End Sub

> >
> >
> >



  #5  
Old January 21st 05, 06:57 PM
chris w
external usenet poster
 
Posts: n/a
Default

link dosent work.

but I have run into a glitch with this function, kinda,

What this tool is doing is I have a button that runs a macro to update links
to the sheet and makes a change to the cell "I7" so "J7" shows when the
update macro was run and I want "L7" to show who ran the update. Using
author, it puts my name in regardless of who (which computer) has the sheet
open. I have another cell that shows who the last author is, but last author
dosent work unless the person saves the sheet before running the update.

I suppose I can have as part of the macro to save sheet?


"Bob Phillips" wrote:

> Chris,
>
> This is the answer I gave to a similar question this morning
>
> http://msdn.microsoft.com/library/de...texcel2000.asp
>
> The object browser in VBA
>
> The VBA Help file (look up Excel Object Model)
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "chris w" > wrote in message
> news
> > Worked like a charm, thanx
> >
> > Where can I find a "dictionary" of VB code or the pop up entries that come
> > up to help complete the code?
> >
> > "Bob Phillips" wrote:
> >
> > > Private Sub Worksheet_change(ByVal Target As Range)
> > >
> > > If Not Intersect(Target, Range("I7")) Is Nothing Then
> > > Range("J7") = Now
> > > Range("L7").Value =

> ActiveWorkbook.BuiltinDocumentProperties("author")
> > > End If
> > >
> > > End Sub
> > >
> > > --
> > >
> > > HTH
> > >
> > > RP
> > > (remove nothere from the email address if mailing direct)
> > >
> > >
> > > "chris w" > wrote in message
> > > ...
> > > > Below is a change declaration I have in a sheet, is it possible to

> have a
> > > > similar change declaration using docProps in another cell? So when

> the
> > > > change is made to cell "I7" the date is put in "J7" and the current

> author
> > > is
> > > > put in "L7"
> > > >
> > > > I tried to copy what I have below except change = Now to =
> > > > DocProps("author") and that didnt work.
> > > >
> > > > Private Sub Worksheet_change(ByVal Target As Range)
> > > >
> > > > If Not Intersect(Target, Range("I7")) Is Nothing Then
> > > > Range("J7") = Now
> > > > End If
> > > >
> > > > End Sub
> > >
> > >
> > >

>
>
>

  #6  
Old January 21st 05, 07:14 PM
Bob Phillips
external usenet poster
 
Posts: n/a
Default


"chris w" > wrote in message
...
> link dosent work.


Try this

http://msdn.microsoft.com/library/de...texcel2000.asp

> but I have run into a glitch with this function, kinda,
>
> What this tool is doing is I have a button that runs a macro to update

links
> to the sheet and makes a change to the cell "I7" so "J7" shows when the
> update macro was run and I want "L7" to show who ran the update. Using
> author, it puts my name in regardless of who (which computer) has the

sheet
> open. I have another cell that shows who the last author is, but last

author
> dosent work unless the person saves the sheet before running the update.
>
> I suppose I can have as part of the macro to save sheet?


No need. I should have thought of that when I was writing it. Updated
version

Private Sub Worksheet_change(ByVal Target As Range)

If Not Intersect(Target, Range("I7")) Is Nothing Then
Range("J7") = Now
Range("L7").Value = Environ("username")
End If

End Sub


Bob


  #7  
Old January 21st 05, 08:01 PM
chris w
external usenet poster
 
Posts: n/a
Default

Bob, your help has been spot on, thank you,

but I believe I may be getting in over my head. The last string change to =
environ("username"), works perfectly, except I realize now my user name on my
XP machine shows "Owner" on the win 98 machines shows nothing. Changing the
"Owner" designation looks to get messy.

"Bob Phillips" wrote:

>
> "chris w" > wrote in message
> ...
> > link dosent work.

>
> Try this
>
> http://msdn.microsoft.com/library/de...texcel2000.asp
>
> > but I have run into a glitch with this function, kinda,
> >
> > What this tool is doing is I have a button that runs a macro to update

> links
> > to the sheet and makes a change to the cell "I7" so "J7" shows when the
> > update macro was run and I want "L7" to show who ran the update. Using
> > author, it puts my name in regardless of who (which computer) has the

> sheet
> > open. I have another cell that shows who the last author is, but last

> author
> > dosent work unless the person saves the sheet before running the update.
> >
> > I suppose I can have as part of the macro to save sheet?

>
> No need. I should have thought of that when I was writing it. Updated
> version
>
> Private Sub Worksheet_change(ByVal Target As Range)
>
> If Not Intersect(Target, Range("I7")) Is Nothing Then
> Range("J7") = Now
> Range("L7").Value = Environ("username")
> End If
>
> End Sub
>
>
> Bob
>
>
>

  #8  
Old January 21st 05, 11:35 PM
Bob Phillips
external usenet poster
 
Posts: n/a
Default

Chris,

Here is an alternative. I can't test it as I don't have Win98 or 95, but it
shoudl work. It does mean that on the 98 machines, you need to ensure that
the Excel user is defined (Tools>Options and on the general tab, set5 the
User Name box).

Private Sub Worksheet_change(ByVal Target As Range)
Dim sOS As String
Dim sSystem As String

If Not Intersect(Target, Range("I7")) Is Nothing Then
sOS = Application.OperatingSystem
Select Case True
Case InStr(1, sOS, "NT 5.01") > 0: sSystem = "XP"
Case InStr(1, sOS, "NT 5.00") > 0: sSystem = "2000"
Case InStr(1, sOS, "NT 4.00") > 0: sSystem = "NT4"
Case InStr(1, sOS, "4.10") > 0: sSystem = "Win98"
Case InStr(1, sOS, "4.00") > 0: sSystem = "Win95"
End Select

Range("J7") = Now
If sSystem = "Win98" Or ssyetm = "Win95" Then
Range("L7").Value = applicationusername
Else
Range("L7").Value = Environ("username")
End If
End If

End Sub

--

HTH

RP
(remove nothere from the email address if mailing direct)


"chris w" > wrote in message
...
> Bob, your help has been spot on, thank you,
>
> but I believe I may be getting in over my head. The last string change to

=
> environ("username"), works perfectly, except I realize now my user name on

my
> XP machine shows "Owner" on the win 98 machines shows nothing. Changing

the
> "Owner" designation looks to get messy.
>
> "Bob Phillips" wrote:
>
> >
> > "chris w" > wrote in message
> > ...
> > > link dosent work.

> >
> > Try this
> >
> >

http://msdn.microsoft.com/library/de...texcel2000.asp
> >
> > > but I have run into a glitch with this function, kinda,
> > >
> > > What this tool is doing is I have a button that runs a macro to update

> > links
> > > to the sheet and makes a change to the cell "I7" so "J7" shows when

the
> > > update macro was run and I want "L7" to show who ran the update.

Using
> > > author, it puts my name in regardless of who (which computer) has the

> > sheet
> > > open. I have another cell that shows who the last author is, but last

> > author
> > > dosent work unless the person saves the sheet before running the

update.
> > >
> > > I suppose I can have as part of the macro to save sheet?

> >
> > No need. I should have thought of that when I was writing it. Updated
> > version
> >
> > Private Sub Worksheet_change(ByVal Target As Range)
> >
> > If Not Intersect(Target, Range("I7")) Is Nothing Then
> > Range("J7") = Now
> > Range("L7").Value = Environ("username")
> > End If
> >
> > End Sub
> >
> >
> > Bob
> >
> >
> >



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Change cell back color on click Dave Peterson Excel Discussion (Misc queries) 0 January 24th 05 10:50 PM
how do i make a date change automatically if i change one before . dpl7579 Excel Discussion (Misc queries) 1 January 11th 05 09:21 PM
How to change the default font and size of "comments"? ClayMcQ Excel Discussion (Misc queries) 1 January 7th 05 11:43 PM
Calculate change over time Gwen Excel Worksheet Functions 0 January 1st 05 05:52 PM
Worksheet Row Change event crazybass2 Excel Discussion (Misc queries) 4 December 8th 04 05:29 PM


All times are GMT +1. The time now is 07:29 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Copyright 2004-2014 ExcelBanter.
The comments are property of their posters.