PDA

View Full Version : VBA CODE ADD TO CODE MANDATORY CELL



stevembe
02-18-2013, 02:09 AM
Hoping someone can help.

I have a worksheet that is using the following code that forces people to complete cells:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
With Sheets("Sheet1")
If WorksheetFunction.CountA(.Range("A1,B6,C4")) <> 3 Then
MsgBox "You must complete A1, B6, C4 "
Cancel = True
End If
End With
End Sub

Is there anyway of adapting this so the message reads:

“You must complete cells A1, B6, C4 unless you opened the workbook to view only”

Then two option buttons saying OK and View File Only. OK will take you back to the sheet to populate the mandatory cells whereas the other exits the workbook without saving any changes.

Bob Phillips
02-18-2013, 02:48 AM
Private Sub Workbook_BeforeClose(Cancel As Boolean)
With Sheets("Sheet1")
If Not Me.ReadOnly Then
If WorksheetFunction.CountA(.Range("A1,B6,C4")) <> 3 Then
MsgBox "You must complete A1, B6, C4 as you didn't open it read-only"
Cancel = True
End If
End If
End With
End Sub

stevembe
02-18-2013, 03:20 AM
Private Sub Workbook_BeforeClose(Cancel As Boolean)
With Sheets("Sheet1")
If Not Me.ReadOnly Then
If WorksheetFunction.CountA(.Range("A1,B6,C4")) <> 3 Then
MsgBox "You must complete A1, B6, C4 as you didn't open it read-only"
Cancel = True
End If
End If
End With
End Sub


Thanks for that but is there a way that on opening I can get it to ask open as read only or open and make changes?

Bob Phillips
02-18-2013, 03:38 AM
How does it get opened? Do you have some code to open it directly, do you have code that they browse and open it, or doe it just get opened from File>Open?

stevembe
02-18-2013, 04:05 AM
How does it get opened? Do you have some code to open it directly, do you have code that they browse and open it, or doe it just get opened from File>Open?

It is opened by File>Open but it would be useful if on opening they could see a message box that says open as 'read only' or open to 'edit'. If they open as read only then it will close but if they open as edit then it prompts them to fill the necessary cells.