Create macro to insert day and hour automatically

Discuss the spreadsheet application

Create macro to insert day and hour automatically

Postby ieta » Fri Feb 22, 2019 6:42 pm

Hi,
I recently moved from office 2010 to openoffice, and found that macros are not compatible. My knowledge of programming is very little, so I needed help.
I needed something very simple, when inserting data into cell A1, for example, in cell C1 would return the correct date and in cell D1, the exact time.
I apologize now, if there is an equal topic, but I did not find any.
Thank you in advance for your attention and willingness to help.
OpenOffice 4.1.6 on Windows 7
ieta
 
Posts: 2
Joined: Fri Feb 22, 2019 6:29 pm

Re: Create macro to insert day and hour automatically

Postby John_Ha » Fri Feb 22, 2019 7:42 pm

Welcome to the forum.

Insert > Function. Choose Date&Time. Select TODAY and NOW etc.

Clipboard03.gif

As a new user you will find much useful information in the User Guides, the Writer, Base and Calc Tutorials and the AOO Frequently Asked Questions. May I suggest you bookmark the pages.

See [Tutorial] Differences between Writer and MS Word files for why you should always work in and save files as .odt.

Showing that a problem has been solved helps others searching so, if your problem is now solved, please view your first post in this thread and click the Edit button (top right in the post) and add [Solved] in front of the subject.
AOO 4.1.6, Windows 7 Home 64 bit

See the Writer Manual, the Writer FAQ, the Writer Tutorials and the Writer guide.

Remember: Always save your Writer files as .odt files. - see here for the many reasons why.
John_Ha
Volunteer
 
Posts: 6592
Joined: Fri Sep 18, 2009 5:51 pm
Location: UK

Re: Create macro to insert day and hour automatically

Postby MrProgrammer » Fri Feb 22, 2019 7:59 pm

ieta wrote:I needed something very simple, when inserting data into cell A1, for example, in cell C1 would return the correct date and in cell D1, the exact time.
Need help with a formula

[Tutorial] Ten concepts that every Calc user should know, especially section 3. Dates in cells

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.
Mr. Programmer
AOO 4.1.5 Build 9789 on MacOS 10.11.6.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Volunteer
 
Posts: 3681
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Create macro to insert day and hour automatically

Postby John_Ha » Fri Feb 22, 2019 8:40 pm

AOO 4.1.6, Windows 7 Home 64 bit

See the Writer Manual, the Writer FAQ, the Writer Tutorials and the Writer guide.

Remember: Always save your Writer files as .odt files. - see here for the many reasons why.
John_Ha
Volunteer
 
Posts: 6592
Joined: Fri Sep 18, 2009 5:51 pm
Location: UK

Re: Create macro to insert day and hour automatically

Postby ieta » Mon Feb 25, 2019 4:22 pm

Thanks for the availability, but I really wanted to replace a macro already made in MsOffice for the language of Openoffice.
The macro is as follows:

Code: Select all   Expand viewCollapse view
Private Sub Worksheet_Change(ByVal Target As Range) 'Update 20140722
Dim WorkRng As Range
Dim Rng As Range
Dim xOffsetColumn As Integer
Dim WorkRng1 As Range
Dim Rng1 As Range
Dim xOffsetColumn1 As Integer

Set WorkRng = Intersect(Application.ActiveSheet.Range("D:D"), Target)
xOffsetColumn = 2
Set WorkRng1 = Intersect(Application.ActiveSheet.Range("E:E"), Target)
xOffsetColumn1 = 2

If Not WorkRng Is Nothing Then
     Application.EnableEvents = False
    For Each Rng In WorkRng
         If Not VBA.IsEmpty(Rng.Value) Then
             Rng.Offset(0, xOffsetColumn).Value = Now
             Rng.Offset(0, xOffsetColumn).NumberFormat = "dd-mm-yyyy"
         Else
             Rng.Offset(0, xOffsetColumn).ClearContents
         End If
     Next
     Application.EnableEvents = True
End If

If Not WorkRng1 Is Nothing Then
     Application.EnableEvents = False
     For Each Rng1 In WorkRng1
         If Not VBA.IsEmpty(Rng1.Value) Then
             Rng1.Offset(0, xOffsetColumn1).Value = Now
             Rng1.Offset(0, xOffsetColumn1).NumberFormat = "hh:mm:ss"
         Else
             Rng1.Offset(0, xOffsetColumn1).ClearContents
         End If
     Next
     Application.EnableEvents = True
End If

End Sub
             Rng.Offset(0, xOffsetColumn).Value = Now
             Rng.Offset(0, xOffsetColumn).NumberFormat = "dd-mm-yyyy"
         Else
             Rng.Offset(0, xOffsetColumn).ClearContents
         End If
     Next
     Application.EnableEvents = True
End If

If Not WorkRng1 Is Nothing Then
     Application.EnableEvents = False
     For Each Rng1 In WorkRng1
         If Not VBA.IsEmpty(Rng1.Value) Then
             Rng1.Offset(0, xOffsetColumn1).Value = Now
             Rng1.Offset(0, xOffsetColumn1).NumberFormat = "hh:mm:ss"
        Else
             Rng1.Offset(0, xOffsetColumn1).ClearContents
         End If
     Next
     Application.EnableEvents = True
End If

End Sub
OpenOffice 4.1.6 on Windows 7
ieta
 
Posts: 2
Joined: Fri Feb 22, 2019 6:29 pm


Return to Calc

Who is online

Users browsing this forum: No registered users and 26 guests