Locking the DBRW in excel

[appleglaze28]

I was wondering is there anyway to as precaution that the DBRW function in the worksheet will always be there? Like even a VBA code or something that can check when a certain cell is missing its DBRW it can check and just copy the formula from the cellabove or below or any other way to do this. Since its only with TM1 Web that the DBRW is guaranteed to retain.

[VRGultom]

DBRW function in the worksheet will always be there
**************
yes. Because DBRW is a TM1 worksheet function

This function retrieves a value from a specified TM1 cube. When all element arguments (e1, e2, etc.) to the function are leaf elements, the DBRW function can also be used to write values to the specified cube, provided that the user has appropriate access privileges to the relevant cube, dimensions, elements, and/or cells.

***************
Like even a VBA code or something that can check when a certain cell is missing its DBRW it can check and just copy the formula from the cellabove or below or any other way to do this
*****************
No, it's different.VBA code is a script to manipulate data in excel. DBRW is only to retrieve or write data to certain cell in the cube. If the cell is not there, it will generate error

[Alan Kirk]

I was wondering is there anyway to as precaution that the DBRW function in the worksheet will always be there? Like even a VBA code or something that can check when a certain cell is missing its DBRW it can check and just copy the formula from the cellabove or below or any other way to do this. Since its only with TM1 Web that the DBRW is guaranteed to retain.

For a report, use worksheet protection to ensure that the user can't accidentally delete the formulas.

For an input sheet, have the users enter into blank cells and use hidden DBS formulas to do the sending rather than having them punch directly into the DBRW formulas. The only down side to this is that it limits your ability to do data spreading, if your users use that. However it's possible to work around that if you really need to.

[appleglaze28]

I know VBA is different is just I thought that maybe there's a way to have VBA automatically check the cell if a DBRW is missing then it will copy the function above. Like for example maybe rather than have tons of line items layed out on a excel sheet. You can probably use a Add button to probably copy the cell from the above DRBW function to the cell row below. Cause this would I think could reduce the calculation time on the sheets.

But thanks for the advise.

[VRGultom]

Oh I c

I don't remember the syntax ( just check the VBA help), but it can.
but the logic is
- as you know the cell address, so you can check the cell value using VBA
and if you find it is not correct, or error take the formula from the other correct cell

Regards
VRG

[Oratia623]

I have had the same (or similar) issue before in that I had an input sheet that needed the cells unprotected, but the users (no matter how many times I told them) would sometimes press the DEL key to remove a value, and this only deleted the formula, not the value in TM1.

I placed the following code into the VBA of the relevant sheet :

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Formula = "" Then
MsgBox "You cannot delete formulas"
Application.Undo
End If
End Sub

Worked quite well for me. Using undo is better than copying from the cell above as they might delete the top formula.
Probably what you are looking for.

Paul

[appleglaze28]

Yeah thanks Paul...but might remove the message box cause people might find it really annoying.

[LoadzaGrunt]

Hi Paul,

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Formula = "" Then
MsgBox "You cannot delete formulas"
Application.Undo
End If
End Sub

The problem with that is that Target.Formula will still return a value where the cell entry was not a formula. (Try entering any number in A1 and trying Cells(1, 1).Formula in the Immediate Window).

This code below still isn’t the best solution (Alan will be along in a moment to update it for error handling at least) but a slight improvement on the above.

Code: Select all

Private strSelectionFormula As String

Const DBRW_PROMPT As String = "I am a DBRW formula - please enter a number"
Const ENABLE_PROMPT As Boolean = True

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    
    Dim rngCell As Range
    Dim strFormula As String
    
    If Target.Cells.Count > 1 Then
        Set rngCell = Target.Cells(1, 1)
    Else
        Set rngCell = Target
    End If
    
    strFormula = rngCell.Formula
    If InStr(1, strFormula, "DBRW", vbBinaryCompare) > 0 Then
        strSelectionFormula = rngCell.Formula
    Else
        strSelectionFormula = ""
    End If
    
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim blnEventSettingState As Boolean
    Dim strCellText As String
    
    strCellText = Target.Text
    
    If strSelectionFormula <> "" And strCellText = "" Then
        blnEventSettingState = Application.EnableEvents
        Application.EnableEvents = False
        Target.Formula = strSelectionFormula
        If ENABLE_PROMPT Then
            MsgBox DBRW_PROMPT, vbOKOnly + vbExclamation, "Warning!"
        End If
        Application.EnableEvents = blnEventSettingState
    End If
    
End Sub

[Oratia623]

Sorry, forgot to add that the sheet was protected and only had the DBRW cells unlocked, thus only DBRW cells could change and I didn't need to check.

I like your solution though.