Macro move to another cell after input

English
#1

Hello,

I have a macro from Excel that offsets to another cell after two inputs but in libreoffice this will select the cell and stop working moving on. Is there any way of having this macro fixed?

Dim mRg As Range
Dim mStr As String

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Range(“E3:R11234”), Target) Is Nothing Then
Set mRg = Target.Item(1)
mStr = mRg.Value
End If
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
Dim xRg As Range
On Error Resume Next
Set xRg = Intersect(Range(“A1:R11234”), Target)
If xRg Is Nothing Then Exit Sub
If Target.Row Mod 2 = 0 Then Target.Offset(-1, 1).Select
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Range(“A1:R11234”), Target) Is Nothing Then
Set mRg = Target.Item(1)
mStr = mRg.Value
End If
End Sub

#2
  1. What other cell?
  2. What do you mean by “after two inputs”.
    I assume an action onAfterInput() won’t know by itself if the input was “the second one”.

Generally don’t expect contributors here to be familiar with VBA and eager to translate (probably silly) VBA code. If you want to get help with something you suppose to need a “macro” for, describe exactly what you actually want/need and wait for an answer telling you if/how you can achieve this in LibreOffice. The best result would be an answer then telling you that you don’t need a macro at all, but can use a ready-made feature.

(See also: XY problem - Wikipedia)

1 Like
#3

For example @HoneyBadger, if your intent is to enter data that is two rows by lots of columns you can actually enter it in another sheet as columns-by-rows (like a normal data table) then transpose that to use in rows-by-columns on your destination sheet.

In the ODS example I’ve including here, go the InputInColumns sheet. Now click anywhere in the data table itself and choose menu Data>Form. Now, hover your mouse just to the right side of the input lines and you’ll see a scrollbar appear. Slam the scrollbar slider to the bottom, and you will have a new record entry form. Now you can zip right through record after record. Use Tab to move to the next field (entry) then Enter on the last field to get yet another record. The new entries will show up in new rows on this sheet, but in columns on the OutputInRows sheet. (I’ve only got it set up for 100 entries, but you get the idea.)

This might not be an ideal solution for you, but I think it proves @Lupp’s point about other ways to do things with no code required.
QuickDataForm.ods (11.6 KB)

#4

@Lupp @joshua4 Sorry for poor explanation. My goal is to fill out two cells down and then offset one to the right automatically due to device being wireless and far away from the computer.

GIF How it should be working:

#5

This post has been moved to Solution.

#6

Just select the working range of cells, input a value and press Enter to move.
Cycles the cursor within the pre-selected range. Macros are not required.

Menu: Tools >> Options - LibreOffice Calc - General - Press Enter to move selection: Down (default).
#7

This doesn’t work because I need to fill out AUTOMATICALLY two cells down and then move diagonally to right and repeat it again as it is show in the GIF

#8

On the contrary, everything works. However, I don’t know what you mean by “need to fill out AUTOMATICALLY”. Or were you not paying attention to the word “pre-selected”?
In my case, the cursor moves as in your example.
I repeat: you don’t need a macro. After all, the topic of the post is different: “Macro move to another cell after input” (not about filling in AUTOMATICALLY).

And I don’t see how this macro (Worksheet_BeforeDoubleClick) is related to the topic of the post. What does double-click have to do with it?

#9

I am using bluetooth caliper to input data into my Excel file and with that macro I move between cells without touching or pressing anyting on the computer itself. Caliper has an ENTER button that submits numbers from it and it inputs to a cell when 2 cells are filled in then the next active cell will be to right.

Pre-defined range works nicely when you can select the range manually and etc. but this input has to be done without human interaction on the Libreoffice Calc that is why I need macro to create offset after having two cells filled in.

My macro works perfectly with Excel but in LibreOffice Calc when it moves to diagonally to right then the cell becomes selected and after entering data input it doesn’t move down automatically when it should be going

#10

(Still edited this post -and played with the maros- while the OQ posted the comment above. Therefore part of the following text may seem obsolete now. I don’t delete it nonetheless because it may throw a light on the fundamental shortcomings of what was offered by the OQ in advance.)

I’m afraid, this won’t lead anywhere. You still didn’t even explain the meaning of the hard-coded ranges E3:R11234 and A1:R11234. You also didn’t mention what kind of “wireless device” (and what interface) you are using. This may be something “emulating” a doubleclick event due to some action. What real action do you perform to trigger an input? Were the ingenious macros you posted probabaly part of “software” delivered with your “device”? Are you still in contact with the person who wrote that code? If vou don’t feel capable of answering my questions, this other person should tell. The volunteers here, not knowing the original goal, actually have the problems they are talking of.

A sheet event like onBeforeDoubleclick() doesn’t exist in Calc.

Once again: Forget your macros, and describe your task (What you eventually want to achieve) The gif doesn’t tell anythinmg new. Read the wikiipedia arcticel (XY…) I linked to above.

A clear explanation still is of interest because the idea of filling many pairs of rows is doubtable fundamentally.

#11

The vendor is offering the device (in Germany) for 79,90EUR and makes his earnings.
Volunteers on this Q&A site offered their time worth some hEUR for free, and there may be many forums (or the like) where similar threads are/were/willbe running.
I feel disappointed about how “community help” is developing.
To give my esteemed colleagues a bit, I attach an example file demonstrating the principles of a Calc style implementation of the two-down-then-onertight-oneup read-in. Not much use, of course.

I’m still wondereing if Excel can do something of the kind without having a sheet for which special event handlers are set. Probably the OQ only missed to tell us about them. It’s a mess.

shortDemoOfDeprecatedConcept.ods (12.0 KB)

#12

When a sheet is protected Calc (and Excel) navigates from one unlocked cell to the next unlocked cell. So unlock your edit cells, then protect the sheet and navigate with Enter and/or Tab key.

Apart from that, databases are made for data collections throug input forms where you have full control over layout and tab order.

#13

Stealing from @eeigor here, but adding the OP request for a more armor-plated telemetric arrangement, what about hiding all but two rows and using Dispatcher uno:SelectAll in an event-fired macro.

Right-click on the sheet tab for Entries sheet to see the event wiring.

TelemetricEntry.ods (9.6 KB)

#14

I can’t do that because my range is from A1:R100000

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.