Page 1 of 1
Locking the DBRW in excel
Posted: Fri May 21, 2010 4:37 am
by 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.
Re: Locking the DBRW in excel
Posted: Fri May 21, 2010 4:52 am
by 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
Re: Locking the DBRW in excel
Posted: Fri May 21, 2010 4:58 am
by Alan Kirk
appleglaze28 wrote: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.
Re: Locking the DBRW in excel
Posted: Fri May 21, 2010 5:22 am
by 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.
Re: Locking the DBRW in excel
Posted: Sat May 22, 2010 1:54 pm
by 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
Re: Locking the DBRW in excel
Posted: Sun May 23, 2010 10:51 pm
by 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
Re: Locking the DBRW in excel
Posted: Mon May 24, 2010 1:23 am
by appleglaze28
Yeah thanks Paul...but might remove the message box cause people might find it really annoying.
Re: Locking the DBRW in excel
Posted: Mon May 24, 2010 3:17 am
by 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
Re: Locking the DBRW in excel
Posted: Mon May 24, 2010 5:35 am
by 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.