PDA

View Full Version : Shared Workbook Which Has 12 Worksheets



Lobo1943
03-15-2018, 04:29 PM
have a shared workbook which has 12 worksheets and each Worksheet has to following VBA code to lock cells after data entry:
Private Sub Worksheet_Change(ByVal Target As Range)

Dim cl As Range

ActiveSheet.Unprotect

For Each cl In Target

If cl.Value <> "" Then

check = MsgBox("is this entry correct? This cell cannot be entered after entering a value.", vbYesNo, "Cell Lock Notification")

If check = vbYes Then

cl.Locked = True

Else

cl.Value = ""

End If

End If

Next cl

ActiveSheet.Protect


End Sub
This works fine when the Workbook is not shared, but when I Share the workbook the code doesn't perform. My question is, "is in possible to share a Wordbook and the code will perform as intended?" Meaning that after data is entered the cell is automatically locked, so other cannot either enter data or delete data.

SamT
03-16-2018, 07:39 AM
Put this code in the Thisworkbook Code Module and delete all the Worksheet_Change in all the worksheets

Option Explicit

'Insert a sheet named "MacroWarning", with a note to allow Macros on it.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Sheets.Visible = False
Sheets("MacroWarning").Visible = True
End Sub

'This sub only runs if Macros are enabled
Private Sub Workbook_Open()
Sheets.Visible = True
Sheets("MacroWarning").Visible = False
End Sub



Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim cl As Range
Dim Check As VbMsgBoxResult

Sh.Unprotect

For Each cl In Target
If cl.Value <> "" Then
Check = MsgBox("is this entry correct? This cell cannot be entered after entering a value.", vbYesNo, "Cell Lock Notification")
If Check = vbYes Then
cl.Locked = True
Else
cl.Value = ""
End If
End If
Next cl

Sh.Protect
End Sub

Lobo1943
03-21-2018, 04:03 PM
Thanks for the suggestion, I appreciate it. As instructed, I created a module (Module 1) and copied and pasted the macro tha you provided. I really new to using VBA code, so I am sure I've done something wrong. I have not been able to get the macro to run. Do you have any suggestions.

Regards, Lowell

SamT
03-22-2018, 09:06 AM
I really new to using VBA code,
Let's set up your VBA editor...
On the menu, open the Tools >> Options item;
On the Editor Tab: Check all boxes in the Code Settings Frame;
On the General Tab: Check all boxes.

On the VBA Menu, make sure that the Project Explorer and the Properties Window are visible. Drag the Properties Window and the Project Explorer to one side of the screen.

On the Code Pane window, (where you type the code,) Take notice of:
The (General) and (Declarations) drop downs at the top of the Pane.

Selecting an item from the (General) list changes the options on the (Declarations) list.

The (Declarations) drop down is a list which lets you navigate the code on that Pane, and, lets you insert Procedure Declarations into your code. It is also a handy way to see all the Event Subs available for the Object, (Workbook, Worksheet, UserForm, and Cass Module,) you are coding with. Selecting an item from the list will scroll to that procedure, or insert an Event Procedure if it doesn't exist.
Subs and Functions are Procedures. "Sub" is shorthand for "Sub Procedure."

On the left efge of the Code Pane is a wide, grey vertical border. Clicking this border next to an executable line of code will set a Breakpoint. Whenever any code is running, it will stop just before the Breakpoint. While the code is running that Procedure, you can Right Click the grey border and select several actions, the one I use most while troubleshooting Code is the "Set Next Statement," which lets me rerun the code from the Statement, (code line) selected to be Next.

If the Cursor is inside a Sub, you can press F5 to run the Sub, or press F8 to step thru the sub or function one line at a time. If you have a Breakpoint set, the F5 choice will stop there, then you can step thru or press F5 to continue. Note that the NEXT line to be executes will be highlighted yellow.

While the code is paused, you can hover the mouse over any variable or anything else that returns a single value, and a Tool Tip will show that value.

Note that you can't Run a Function Procedure by itself. I use a Test Sub Procedure to troubleshoot functions.

Sub Test_FunctionName()
Dim X
X = FunctionName(Arguments)
End Sub
I merely set Breakpoint at the End Sub Line, then, with the Cursor inside the Test Sub, press F5. The code will stop executing at the end of the Sub and I hover over the X variable to see the results. If needed, I then set Breakpoints in the Function.

Pressing F1 will, (may,) open the help file. If the Cursor in in or directly next to, any Key word, The help for that term will, (may,) be displayed. In UserForfs, selecting any Obect, (control,, the UF itself,) will display help for that Object. In the Properties Window, selecting any Property works the same.

Pessing F2 opens the Object Browser, which is a way to examine all the "things" in Excel and VBA and any referenced "things" in that VBA Project. A sophisticated technical library, if you will.

NOW, to troubleshoot that code above... You said
As instructed, I created a module (Module 1) and copied and pasted the macro tha you provided.

I had said
Put this code in the ThisWorkbook Code Module and delete all the Worksheet_Change in all the worksheets
Code Module and Code Pane and Code Page all mean the same thing.
See the problem?