Tom's Guide | Tom's Hardware | Tom's Games
 |
 |
 |
ok now this one i dont think is posible...but i wish it were...
im in a network situation where we have an excel workbook used by 2 employees, both employees make changes to the excel sheet through out the day and currently before we make any changes to the workbook we have to close and reopen it.
is there a way to have excel update an open workbook on one computer with out closing and reopening it when a change is made from another computer?
ok i have an idea but dont know how to make it work or even if it can...
computer 1 opens excel file
computer 2 opens excel file and gets a message that the excel workbook is locked for editing and gives 3 options ( read only, notify, cancel) excel automatically chooses notify
computer 1 closes file computer 2 receives pop up letting computer 2 know that computer 1 has closed excel and gives 2 options (read-write, cancel) excel automatically chooses read-write
when computer 1 closes file it gives computer 2 time for the above process to take place then opens excel again automatically (knowing that excel is already open computer 1 automatically chooses notify)
i know that that could be kind of confusing but that would be the best way that i can think of to explain it. if any one needs clarification i would be happy to comply to the best of y ablity
0 
Set up sharing for the workbook. You didn't specify which version... Tools, Share Workbook is where you set it in 2003.
"So won’t you give this man his wings
What a shame
To have to beg you to see
We’re not all the same
What a shame" - Shinedown
0
lol im sorry but thank you that did work and i didnot know about that but it did not quite work compleatly.. its my fault because i was not specific enough...i do have 2003 but in the work book that i need this for i have movable objects and macros that i need to work, when i share it that way they dont work is there any way around that?
0
As per the Help files the following features are not available in a Shared Workbook:
- Use drawing tools - You can view existing drawings and graphics.
- Write, record, change, view, or assign macros - You can run existing macros that don't access unavailable features.
If your macros aren't working, it may be because they are trying to use one or more of the features that are unavailable in a Shared Workbook. Perhaps those features can be eliminated from the macros if they are not as important as some of the other things that the macros do.
Without knowing exactly what you are doing re: the moveable objects, I can't think of any workarounds for that.
0
ok thank you...i can use the share workbook for something though...is there a macro i can write or a setting that i can change to have excel save the workbook like every 30 seconds?
0
First, if you are simply looking to change the AutoSave increment for file recovery purposes, just look up AutoSave in the Help files.
However, if you really want to save the file every 30 seconds, using it's real name, just as if the user clicked the Save button, then these pieces of code should do what you ask.
However, without knowing what else you are doing in the workbook (other macros, etc.) I can't say that they will play well with others.
Taken as a stand-alone setup, they will save your workbook every 30 seconds from the time it is opened.
Note 1: It will not save the workbook when the user is entering data in a cell, or has a menu open, etc. In those cases it will wait until the user is done with that particular action before it will save. In other words, if the user types something in the cell but doesn't hit the enter key, and then goes to lunch, the code will just sit there until the user finishes that action.
Note 2: Very often - I say again *very often* - I make changes to my workbooks that I do not want saved. I might be testing a formula, I might be parseing some data, I might delete important data, etc. It would be extremely annoying and potentially very dangerous if I opened a workbook that saved itself every 30 seconds.
All that said, here you go...
These first 2 pieces of code go in the ThisWorkbook module of the workbook.
Private Sub Workbook_Open()
'Start Timer Code When Workbook Opens
ThisWorkbook.my_SaveTimer
End Sub
Sub my_SaveTimer()
'Timer Code That Calls my_SaveCode macro
Application.OnTime Now + TimeValue("00:00:30"), "my_SaveCode"
End SubThis piece of code goes into any standard VBA module (in the same workbook) such a Module1, etc.
Sub my_SaveCode()
'Save Workbook
ActiveWorkbook.Save
'Start the Timer Again
ThisWorkbook.my_SaveTimer
End Sub
0
its basiclly so that i can chat in a shared workbook with out having to click save to see if some one wrote to me
this works great and wonderful thank you all so much
0
Just as long as you are aware that any haphazard key strokes or temporary entries are going to get saved with no warning to the user.
0
derbydad thank you for that information...is there anything that you can help me add to it to make the title bar flash in the task bar when it automaticly saves??
0
lol so i havent gotten to far on this but i am tring...so far heres what i have for this
private sub worksheet_change()
'what the hey can i put here
end sub
lol any help from here would definatly help. im going to continue working on this in the mean time... i will be sure to let you guys know if i get it
0
re: help you "make the title bar flash in the task bar"
Well, since the title bar is the bar across the top of a window that displays the name of the document and program you are working with, and the taskbar is the area (typically) across the bottom of your screen that displays the Start menu and currently running programs, no, I can't help you "make the title bar flash in the task bar".
I doubt anybody can.
0
ok all i need help...i have an idea that i think will work but i cant put it together allone
when a message box pops up when excel is not the active appplication it causes the title bar to flash the way i would like
i just need help filling in the blanks i guess...here is what i have so far{called by my_save code as seen above}
sub changeCheck()
for x = 1 to 35
for y = 1 to 10
cells(x,y) = [please see "help 1"] then msgbox [what ever i put here]
[please see "help 2"]
next
[please see "help 3"]
next
end subhelp 1: i need to check to see if a cell has been changed...i was thinking for this i could use comments in the cells containing a time stamp of when the cell was last changed set by Worksheet_change which i would need help writing
help 2: i would have to have something here along the lines of if the msgbox pops up then end the loop
help 3: same as help 2
...so there is my idea but i really need help if any one could please give me a hand i think this could work...
0
re: help you "make the title bar flash in the task bar"
TweakUI can do this. It's under the General Tab.
Download the XP Power Tools and install TweakUI.
Just re-read the post. May not work on AutoSave.
I have lost friends, some by death... others through sheer inability to cross the street.
- Virginia Woolf
0
If you want to pop up a MsgBox when a cell within a specific range has changed, you don't have to loop through the range. The Intersect method can be used instead.
Application.Intersect(Range("rg1"), Range("rg2")) will either return Nothing (if the ranges don't intersect) or a whole bunch of stuff about the cells within the intersection if they do.
Therefore if "Not" the result of the Intersect method is "Nothing", then it must be something, meaning the ranges must intersect. Makes sense to me. ;-)
Anyway, this will tell you the address of the cell (or cells) that changed if that cell (or cells) is within (i.e. intersects) the specified range.
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Range("A1:J35")) Is Nothing Then
MsgBox Target.Address
End If
End Sub
0
thank you derbydad for that knowledge regarding the intersect method... i did not even know about that...the only thing that i can see that might be a problem with this is if a cell is not blank then it will not pop up the msgbox....
0
...let me rephrase the original question...
in a shared work book is it possible to make a msgbox pop up when a change is made by another user?
0
ALRIGHT!!! I got it to flash the way i wanted. the code used is as followed:
in worksheet W :
Dim nowUpdating1 As Boolean
Dim nowUpdating2 As Boolean
Dim Alright As BooleanPrivate Sub Worksheet_Activate()
BeginUpdate1
Updating1
End SubPrivate Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo 4
W.Name = "SENDING...."
ThisWorkbook.Save
Alright = True
4
Good (Alright)
Alright = False
ChangeFrm.Show
End SubPrivate Sub Worksheet_Deactivate()
EndUpdate
W.Name = "Work"
End SubPublic Sub BeginUpdate1()
nowUpdating1 = True
nowUpdating2 = False
End SubPublic Sub BeginUpdate2()
nowUpdating1 = False
nowUpdating2 = True
End SubPublic Sub EndUpdate()
nowUpdating1 = False
nowUpdating2 = False
End SubPublic Sub Updating1()
If nowUpdating1 = True Then _
Application.OnTime Now + TimeValue("00:00:07"), "Update1"
End SubPublic Sub Updating2()
If W.Name = "Work" Then BeginUpdate1
If nowUpdating2 = True Then _
Application.OnTime Now + TimeValue("00:00:07"), "Update2"
End SubPublic Sub Change()
If W.Name = "SENDING...." Then ChangeTrue
End SubPublic Sub ChangeTrue()
EndUpdate
ChangeFrm.Show
End Subin userform ChangeFrm :
Private Sub ChangeOK_Click()
W.Name = "Work"
ThisWorkbook.Save
W.BeginUpdate1
W.Updating1
ChangeFrm.Hide
End SubPrivate Sub ChangeXcl_Click()
ThisWorkbook.Save
W.BeginUpdate2
W.Updating2
ChangeFrm.Hide
End Subin module Updating :
Dim Alright As Boolean
Sub Update1()
On Error GoTo 1
ThisWorkbook.Save
W.Change
Alright = True
1
Good (Alright)
End SubSub Update2()
On Error GoTo 1
ThisWorkbook.Save
W.Updating2
Alright = True
1
Good (Alright)
End SubSub Fallout()
H.Select
End SubSub Good(Stopped As Boolean)
If Stopped = False Then Update2
If Stopped = True Then Alright = False
End SubIt took some work to make it work but it was well worth it. thank you every one for the help that was given
0  |
 |
 |
This post is quite old and has been locked from receiving new replies. Please create a new posting instead.
| Ads by Google |
