Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Shared Workbook
In a shared work, I would like a user to be able to double-click on a
cell in a particular column, and if the cell is blank, the user's username is entered. But if the cell has had something entered in it by another user, the program will detect that and offset to a cell below the selected one. I am getting close to what I want with the following but it is not working perfectly. Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) ActiveWorkbook.Save If Target.Column = 3 Then If Selection.Value = "" Then Selection.Value = Environ("username") Else Selection.Offset(1, 0).Select ActiveCell.Value = Environ("username") End If End If ActiveWorkbook.Save End Sub The first workbook save should make other users' entries show up on the current screen but this has not been preventing overwriting. Also, it will probably be necessary to skip more than one line with entries sometimes so I think it needs a loop of some kind. Further, it appears that cutcopy mode stays on when selection is an empty cell, but turns off when selection is a cell with something in it. Any help would be appreciated. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Shared Workbook
Saving the workbook doesn't really do anything as far as showing what some
other user might be putting into the cell. However, if you save, close and re-open the workbook, you should have the lastest version of the shared workbook. But that is a lot of maneuvering when you could have it check that cell upon initially opening (Workbook_Open event) the workbook and enter the user data at that point. I haven't operated on a net in a long time, but does it not restrict other users to read only while one user has a read/write version open? If that is true, then the using the workbook open event should avoid any overwrites. "Slim Slender" wrote in message ... In a shared work, I would like a user to be able to double-click on a cell in a particular column, and if the cell is blank, the user's username is entered. But if the cell has had something entered in it by another user, the program will detect that and offset to a cell below the selected one. I am getting close to what I want with the following but it is not working perfectly. Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) ActiveWorkbook.Save If Target.Column = 3 Then If Selection.Value = "" Then Selection.Value = Environ("username") Else Selection.Offset(1, 0).Select ActiveCell.Value = Environ("username") End If End If ActiveWorkbook.Save End Sub The first workbook save should make other users' entries show up on the current screen but this has not been preventing overwriting. Also, it will probably be necessary to skip more than one line with entries sometimes so I think it needs a loop of some kind. Further, it appears that cutcopy mode stays on when selection is an empty cell, but turns off when selection is a cell with something in it. Any help would be appreciated. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Shared Workbook
Here's something I can't figure out.
If I run this by double-clicking in an empty cell, it enters the username then stays in edit mode with the cursor flashing in the cell. When I duoble click on a cell that has something in it, the program does what it is supposed to do and skips down to an empty cell and enters the username and "finishes", the cursor not flashing. I would like to have them both "finish". How is that controlled? Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) If Target.Column = 3 Then Do If Selection.Value < "" Then Selection.Offset(1, 0).Select End If Loop Until Selection.Value = "" Selection.Value = Environ("username") End If End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Shared Workbook
On 12 Ago, 02:27, Slim Slender wrote:
Here's something I can't figure out. If I run this by double-clicking in an empty cell, it enters the username then stays in edit mode with the cursor flashing in the cell. When I *duoble click on a cell that has something in it, the program does what it is supposed to do and skips down to an empty cell and enters the username and "finishes", the cursor not flashing. I would like to have them both "finish". How is that controlled? Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) * * If Target.Column = 3 Then * * * * Do * * * * * * If Selection.Value < "" Then * * * * * * * * Selection.Offset(1, 0).Select * * * * * * End If * * * * Loop Until Selection.Value = "" * * * * Selection.Value = Environ("username") * * End If End Sub Hi Slim. Probably, due to my poor english, I have not understood. However try to insert the instruction: Cancel = True as first instruction in the macro. Regards Eliano |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Shared Workbook
On Aug 11, 8:40*pm, eliano wrote:
On 12 Ago, 02:27, Slim Slender wrote: Here's something I can't figure out. If I run this by double-clicking in an empty cell, it enters the username then stays in edit mode with the cursor flashing in the cell. When I *duoble click on a cell that has something in it, the program does what it is supposed to do and skips down to an empty cell and enters the username and "finishes", the cursor not flashing. I would like to have them both "finish". How is that controlled? Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) * * If Target.Column = 3 Then * * * * Do * * * * * * If Selection.Value < "" Then * * * * * * * * Selection.Offset(1, 0).Select * * * * * * End If * * * * Loop Until Selection.Value = "" * * * * Selection.Value = Environ("username") * * End If End Sub Hi Slim. Probably, due to my poor english, I have not understood. However try to insert the instruction: Cancel = True as first instruction in the macro. Regards Eliano- Hide quoted text - - Show quoted text - Thanks Eliano, that works. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
shared workbook is set up is not 'shared' on network | Excel Worksheet Functions | |||
shared workbook - shared workbook options grayed out | Excel Discussion (Misc queries) | |||
Shared Workbook - Slow opening of shared workbook. | Excel Programming | |||
Printing viewing a shared workbook on a shared drive | Excel Discussion (Misc queries) | |||
update pivot in shared shared workbook | Excel Discussion (Misc queries) |