Shared Workbook

S

Slim Slender

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.
 
J

JLGWhiz

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.
 
S

Slim Slender

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
 
E

eliano

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
 
S

Slim Slender

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.
 

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