Changing Cell format (Value to String then back to Value)

[n00dnutt]

Hi Team

I have a two part problem/question relating to a cell that has 3 steps:

1. Starts with formula: “=Coding!$AK$13”
2. Coverts to a (0) zero Value.
3. Reverts back to having the above formula.

Steps .1 and .2 work as expected, but! I am having issues when using .SetFormula for Step .3.

Here what’s supposed to happen:
When the user clicks “Take Win”, the amount is added to the “Credit” Balance and sC02 is set to zero.

Sub cmdBtn_TakeWin

    Dim sN01 As Object  
    Dim sR01 As Object
    Dim sR02 As Object
    Dim sC01 As Object
    Dim sC02 As Object 
  
    Dim iCurVal As long
    Dim iWins as Long
    Dim iNewVal As long
        
    sN01 = ThisComponent.CurrentController.ActiveSheet
    sR01 = sN01.getCellRangebyName("G10")
    sR02 = sN01.getCellRangebyName("O10")    
    sC01 = sR01.getCellByPosition(0,0)   
    sC02 = sR02.getCellByPosition(0,0)
    
    iCurVal = sC01.Value
    iWins = sC02.Value
    iNewVal = iCurVal + iWins
  
    sC01.Value = iNewVal
    sC02.Value = 0

End Sub

When the user clicks “Play”, Step .3 inserts the formula.

Do I need to change the attribute of the cell…???

Sub cmdBtn_Play

    dim document   as object
    dim dispatcher as object
    
    document   = ThisComponent.CurrentController.Frame
    dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
    
    dispatcher.executeDispatch(document, ".uno:Calculate", "", 0, Array())

REM----------------------------------------------------------------------      
        
    Dim sN01 As Object 
    Dim sR01 As Object
    Dim sR02 As Object                
    Dim sR03 As Object
    Dim sC01 As Object
    Dim sC02 As Object 
    Dim sC03 As Object 
    
    Dim iCurVal As long
    Dim iBet as Long
    Dim iNewVal As long 
        
    sN01 = ThisComponent.CurrentController.ActiveSheet    
    sR01 = sN01.getCellRangebyName("G10")
    sR02 = sN01.getCellRangebyName("K10")  
    sR03 = sN01.getCellRangebyName("O10")  
    sC01 = sR01.getCellByPosition(0,0)   
    sC02 = sR02.getCellByPosition(0,0) 
    sC03 = sR03.getCellByPosition(0,0) 
    
    iCurVal = sC01.Value
    iBet = sC02.Value  
    iNewVal = iCurVal - iBet 
    sC01.Value = iNewVal 
        
    sR03 = sN01.getCellRangebyName("O10")        
    sC03 = sR03.SetFormula = "=Coding!$AK$13"

                     
End Sub

TIA
Mark

[JohnSUN]

@n00dnutt

sC03 = sR03.SetFormula = "=Coding!$AK$13"

How would you execute this line yourself in BASIC’s place?

[n00dnutt]

Hi @JohnSUN
Many thanks. I actually tried this prior and! although it does insert the formula, it’s as if the cell has been coverted to a string as the formula doesn’t calculate.

The sheet recalcs each time the user clicks “Play”.

Can you drop an example on how I can set the cell .NumberFormat to = “0” please.

TIA
Mark.

[JohnSUN]

I’m about to write words that may seem offensive. But please don’t be offended.
Yes, I could create a spreadsheet with two sheets: Demo and Coding. There would be a cell that would take the value from cell AK13 and sum something in a macro. But I won’t do that. Simply because it’s boring and most likely won’t look like your spreadsheet. Just attach an example to the next comment or to the original question, and any of us will be happy to continue this discussion. But creating a test example by a verbal description is most often a waste of time and effort.

[n00dnutt]

Hi @JohnSUN I fully appreciate what your saying. Please don’t stress over offending me, my skin is way too thick, plus! I’m way too old to be offended… myPokies_1.ods (22.9 KB)

TIA
Mark.