How to disable Save Data in EPM if the EPMMemberProperty is not equal to LOB

[jackyysteez]

So I'm new to VBA but I was given the task to make it so if somebody puts the wrong cost center into cell C27 and it does not fall into the right LOB category, a message box pops up and tells you you may not save due to cost center entry. The code should also cancel the save action unless a correct Cost Center is provided. So far my code is...

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)


Public Property Get CostCenter() As String
Set CostCenter = Range("C27")
strCostCenter = obj.CostCenter
variable = Object.Property

CostCenter.Select.GetPropertyList
If CostCenter <> LOB_400 Then
MsgBox "Wrong Cost Center provided!", vbCancel
Cancel = True
Else: Cancel = False
End If

End Property

End Sub

However I am having immense trouble with it as every time i try and run it a Pop Up occurs asking me to name the Macro.. Let me know what you guys think, Thank you!!

 

[Chameleon64]

Could you use Data Validation in cell C27 to check the value at the point of data entry? Easier than VBA!

 

[jackyysteez]

I'm not sure what type of Data Validation would be necessary, I'm fooling around with it now trying to get a feel for it, but what would i do when it comes to using the Data Validation instead of VBA

 

[Chameleon64]

I was thinking you could have a list of valid cost centres, create a named range for them and then use Data Validation to check that the entry is in the list.

 

[Norie]

Why are you using Public Property Get?

Is there a class involved somewhere?

Also, what are obj and Object referring to here?

strCostCenter = obj.CostCenter
variable = Object.Property

 

[jackyysteez]

I actually have since deleted both of those objects, and changed it to Private Property Get

 

[Norie]

Why are you using Private Property Get?

If you wanted to check the value in a cell I don't see why you would need that, but then again I might be missing something.

Also, if you want to prevent a file being saved then you should probably be looking at using something like the workbook BeforeSave event.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

End Sub

 

[jackyysteez]

I was looking for the name of the Cell not the value. However even when I use

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)    If Range("C27").Name <> LOB_400 Then
        Cancel = True
    Else
        Cancel = False
End Sub

I get the macro naming pop up occurring

 

[Norie]

To get the value of a cell you would use, well, the .Value property.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)    

    If Range("C27").Value <> "LOB_400" Then
        MsgBox "Incorrect cost centre, save cancelled!"
        Cancel = True
    Else
        Cancel = False
    End If

End Sub