English
Ask Your Question
1

VBA code - create automatically a new row - does not work in LibreOffice

asked 2012-11-22 12:49:44 +0200

leukerdt gravatar image

Hi

In MS-Excel I ‘m using a spreadsheet with a VBA-code (which I did not create myself) whereby automatically a row is created under the row where data is inserted when, in column B, I insert whether “i” or “u” (without the brackets). The code is :

Private Sub Worksheet_Change(ByVal Target As Range) Dim Rij As Integer, Kolom As Integer Dim Waarde As String Rij = Target.Row Kolom = Target.Column If Kolom = 2 Then Application.ScreenUpdating = False Waarde = UCase(Trim(Target.Value)) If Waarde = "I" Or Waarde = "U" Then Application.CutCopyMode = False Rows(Rij & ":" & Rij).Select Selection.Copy Selection.Insert Shift:=xlDown Rows(Rij + 1 & ":" & Rij + 1).Select Selection.PasteSpecial Paste:=xlPasteAllExceptBorders, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False Selection.ClearContents Range("C" & Rij).Select Application.CutCopyMode = True End If Application.ScreenUpdating = True End If

(Some translations because the code is in Dutch : Rij = Row / Kolom = Column / Waarde = Value)

The reason for this code :

  • always leave two rows open/blank between the row where data is being inserted and the sum/total of the columns F and G
  • lowering the sum/totals in F and G automatically so I don’t have to create a row manually (“Insert Row”) to maintain two open/blank rows.

In LibreOffice this VBA-code works almost. (first the security for macro’s has to be set to low via extra/libreoffice/security).

When I insert “i” or u” the new row is created underneath but all the inserted data (a date in column A and “i” or “u” in column B) is deleted.

I already mentioned this problem to the developers at Novel Gooo and the were about to fix it but then this project was discontinued.

For me solving this problem is absolutely necessary to abandon MS.

Does anyone has a solution ?

Thx

Luc

edit retag flag offensive close merge delete

1 answer

Sort by » oldest newest most voted
1

answered 2012-12-12 11:33:15 +0200

qubit gravatar image

Hi @leukerdt,

just taking a quick crack at cleaning up the formatting on that code to make it easier for us to help you out.

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Rij As Integer, Kolom As Integer
  Dim Waarde As String

  Rij = Target.Row
  Kolom = Target.Column

  If Kolom = 2 Then
    Application.ScreenUpdating = False
    Waarde = UCase(Trim(Target.Value))

    If Waarde = "I" Or
       Waarde = "U" Then
      Application.CutCopyMode = False
      Rows(Rij & ":" & Rij).Select
      Selection.Copy
      Selection.Insert
      Shift:=xlDown
      Rows(Rij + 1 & ":" & Rij + 1).Select
      Selection.PasteSpecial

      ' Not sure how to read/format this comma-delimited part '
      Paste:=xlPasteAllExceptBorders,
      Operation:=xlNone,
      _ SkipBlanks:=False,
      Transpose:=False

      Selection.ClearContents
      Range("C" & Rij).Select
      Application.CutCopyMode = True
    End If

    Application.ScreenUpdating = True
  End If
End Sub
edit flag offensive delete link more

Comments

@qubit: How did you create the grey backgound insert?

ROSt52 ( 2012-12-13 01:43:09 +0200 )edit

@ROSt52 -- Indent at least 4 spaces (and keep a blank line between any content above). You can also just click the "101..010" ("<pre>") button in the answer box GUI

qubit ( 2012-12-13 01:54:11 +0200 )edit

@qubit: Thanks for immediate reply. Indenting is understood. But what is "101...101"("pre") button in the answer box GUI? In which application are writing this text? How do you insert your result with the grey background. I am curious because this appears to me as a good way to explain things.

ROSt52 ( 2012-12-13 02:51:15 +0200 )edit

@ROSt52 -- In the interface for providing an Answer to a Question, there's a row of formatting buttons -- B, I, etc.. The one for formatting text as a <code> or <pre> block is the 5th one and looks like this:

101
010
qubit ( 2012-12-13 03:14:29 +0200 )edit

@qubit:Thanks for your explanations. I was thinking you use a completely different application and than insert the whole thing. I recently got a list of the functions of the buttons and need to study and apply this more often.

ROSt52 ( 2012-12-13 04:16:40 +0200 )edit
Login/Signup to Answer

Stats

Asked: 2012-11-22 12:49:44 +0200

Seen: 1,066 times

Last updated: Dec 12 '12