Bug 122378

Thank you for reporting the bug. Please attach a sample document, as this makes it easier for us to verify the bug. 
(Please note that the attachment will be public, remove any sensitive information before attaching it. 
See https://wiki.documentfoundation.org/QA/FAQ#How_can_I_eliminate_confidential_data_from_a_sample_document.3F for help on how to do so.)

I have set the bug's status to 'NEEDINFO'. Please change it back to 'UNCONFIRMED' once the requested document is provided.

Created attachment 147900 [details]
complete sheet for different month and different payout

This sheet is used for additional payout.
Cell C14 used to change date for any month; Conditional formatting fail when change
Cell L6 use P57:P59 select region fails when changed; Conditional formatting fail when change

VLOOKUP(C14,Region,1,0) D14:D44 
after change VLOOKUP(C14,#NAME?,1,0) after change never turns on ConditionalStype_1
	

VLOOKUP(C14,Region,1,0) F14:F44 turns on ConditionalStype_2
after change VLOOKUP(C14,#NAME?,1,0) after change does not turn on ConditionalStype_2

Cell I6 change value does work use P65:P66
Formulas in sheet
B14:B44
=IF(ISNA(VLOOKUP(C14,Region,2,0)),"",VLOOKUP(C14,Region,2,0))

C14:C44 date and Cell +1

D14:F44
Conditional formatting

H14:H44
=IF(OR(WEEKDAY($C14,2)>5,D14>="""a"),$G$57,$H$57)

O14:O44
=SUM($J$13*J14)+($K$13*K14)+($L$13*L14)+($M$13*M14)+($N$13*N14)

Row 49:50  summery of each row

D58
=TEXT(C14," MMMM")

D61
=CONCATENATE(C58,D58)

G57
=IF($I$6="yes",G58,G59)

H57
=IF($I$6="Yes",H58,H59)


Sheet Input list different Regions with different input for December 18 to December 2019



VBA
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.Count > 1 Then Exit Sub
    If Intersect(Target, Range("$C$14", "$L$6")) Is Nothing Then Exit Sub
    
Dim cell As Range
'Dim rng As Range
Dim RangeName As String
Dim CellName As String
Dim ChRange As String
    ChRange = Range("L6").Value
    
'Range of Cells Reference (Workbook Input)
If ChRange = "DS" Then
  RangeName = "Region"
  CellName = "B4:C23"
    Set cell = Worksheets("Input").Range(CellName)
    ThisWorkbook.Names.Add Name:=RangeName, RefersTo:=cell
 
 Else
    If ChRange = "SR" Then
    RangeName = "Region"
    CellName = "H4:I23"
      Set cell = Worksheets("Input").Range(CellName)
      ThisWorkbook.Names.Add Name:=RangeName, RefersTo:=cell
  Else
        If ChRange = "TI" Then
        RangeName = "Region"
        CellName = "M4:N23"
          Set cell = Worksheets("Input").Range(CellName)
          ThisWorkbook.Names.Add Name:=RangeName, RefersTo:=cell
    End If
  End If
End If
' Kopieren Makro
    Range("B14:B44").Select
    Selection.Copy
    Range("D14:D44").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
     Range("G14").Select
     Application.CutCopyMode = False
     ActiveSheet.Name = Range("D61").Value
End Sub

Added my sheet and described my formulas.

steps to reproduce:
- allow macro execution: Tools/Options/LibreOffice/Security/Macro Security
- allow VBA Macro execution: Tools/Options/Load/Save/VBA Properties 
- restart LO
- open spreadsheet
- select "SR" from cell L6
- macro "Worksheet_Change(Range)" starts

it seems adding the already existing named range "Region" with 
the new range causes lo to invalidate the conditional formula:
VLOOKUP(C14,#NAME?,1,0) 

i am not familiar with excel vba macro's but this seems to work for me:

'   ThisWorkbook.Names.Add Name:=RangeName, RefersTo:=cell
    ThisWorkbook.Names.Add Name:="tmp", RefersTo:=cell
    ThisWorkbook.Names("tmp").Name = RangeName

first add a new named range "tmp" pointing to the new range,
then rename it to "Region".

i tend to say this is not an lo issue.

(In reply to Oliver Brinzing from comment #4)
> i am not familiar with excel vba macro's but this seems to work for me:
> 
> '   ThisWorkbook.Names.Add Name:=RangeName, RefersTo:=cell
>     ThisWorkbook.Names.Add Name:="tmp", RefersTo:=cell
>     ThisWorkbook.Names("tmp").Name = RangeName

please ignore this, seems not to work

maybe changing the cell reference will help:

Rem  ThisWorkbook.Names.Add Name:=RangeName, RefersTo:=cell
ThisWorkbook.Names("Region").RefersTo = "=Input!$B$4:$C$23"

Rem  ThisWorkbook.Names.Add Name:=RangeName, RefersTo:=cell
ThisWorkbook.Names("Region").RefersTo = "=Input!$B$4:$C$23"

Does not really help using LibreOffice Calc but at least VLOOKUP(C14,#NAME?,1,0) ,#NAME? is now Region but still does not update.
Works perfect using Excel.

It seems the problem not updating the vlookup formula is also know in bug 122394 as you sent me. 
Please enplane in more detail this and how to use lo api using my example.
btw.:
the problem does not happen if named ranges content is changed via lo api,
see "test_vlookup_changed_range.ods"

(In reply to Heinrich Padrutt from comment #7)
> Please enplane in more detail this and how to use lo api using my example.

you can for - example change - the macro code like this:

If ChRange = "DS" Then
 ThisComponent.NamedRanges.getByName("Region").Content = "$Input.$B$4:$C$23"
 Else
  If ChRange = "SR" Then
     ThisComponent.NamedRanges.getByName("Region").Content = "$Input.$H$4:$I$23"
  Else
     If ChRange = "TI" Then
       ThisComponent.NamedRanges.getByName("Region").Content = "$Input.$M$4:$N$23"
    End If
  End If
End If

but as mentioned in issue https://bugs.documentfoundation.org/show_bug.cgi?id=122420 it seems the vba "worksheet_change" listener is lost.
so you need to do the code change with excel.

if you want to work with LO IMHO the best solution would be to exchange the vba code with LO basic code an save with ods file format.

(In reply to Oliver Brinzing from comment #8)
> (In reply to Heinrich Padrutt from comment #7)
> > Please enplane in more detail this and how to use lo api using my example.
> 
> you can for - example change - the macro code like this:
> 
> If ChRange = "DS" Then
>  ThisComponent.NamedRanges.getByName("Region").Content = "$Input.$B$4:$C$23"
>  Else
>   If ChRange = "SR" Then
>      ThisComponent.NamedRanges.getByName("Region").Content =
> "$Input.$H$4:$I$23"
>   Else
>      If ChRange = "TI" Then
>        ThisComponent.NamedRanges.getByName("Region").Content =
> "$Input.$M$4:$N$23"
>     End If
>   End If
> End If
> 
> but as mentioned in issue
> https://bugs.documentfoundation.org/show_bug.cgi?id=122420 it seems the vba
> "worksheet_change" listener is lost.
> so you need to do the code change with excel.
> 
> if you want to work with LO IMHO the best solution would be to exchange the
> vba code with LO basic code an save with ods file format.

Hi Oliver,
Do you think this is a valid bug ?

(In reply to Xisco Faulí from comment #9)
> Do you think this is a valid bug ?

i think if:

>  ThisWorkbook.Names("Region").RefersTo = "=Input!$B$4:$C$23"

will keep the formula valid (still pointing to "Region") we can not call it a bug, cause removing a named range will make references invalid.

the main problem seem to be the missing formula update as described in issue:
Bug 122420 - FILESAVE: VBA Worksheet_Change Listener lost on saving .xlsm in LO

Closing as dupe of bug 122420 then

*** This bug has been marked as a duplicate of bug 122420 ***