This is a redo from a previous question but the answer given was in Python and the other macros I have are all in Basic.
In my worksheet on the “Start” page I have a selection (Data Validation) list in J12 . When I find the selection I want, I want to be able to activate the macro that will put the selection into the next empty cell in a range of cells that go from J13 to J36, then clear the selection in J12 (but not remove the formatting or completely delete the full selection list). By clear the selection it will go to the empty field included in the selection list.
of course it can, if you had a minimal clue about Basic, you could translate it yourself,
.
Please study the API function of the LO. You had got many sample code snippets - based on the StarBasic - at your another question . Most of the are usable in this task too.
.
.
I suggest you to download, install and use the MRI or XrayTool. The you will able to examine the existing properties, methods, and interfaces of the programming objects. And study the API descriptions, and Andrew Pitonyak’s free macro books.
.
Then you will able to GET some cell ranges; to GET the cells in the range one-by-one (cyclically); to CHECK if the cells are empty or not, and to PUT a value into the cell by your macro subroutine.
.
I suggest you to use Form Control Element for this task instead of the Data Validity feature: then you will assign the macro to an event of the Form Control.
I don’t have time to do all that. I am just trying to get this sheet to work. No, I do not have any real understanding of Basic or much of any other programming language, just snippets of things here and there.
There was another thread where I asked about adding numbers in and I asked if I could use something like that and someone did respond but they put it in Python.
I want to put the macro in a button because I do not want it to activate unless I decide to use it or not. Just because a selection is made does not mean it will be what is needed.
Here is an another approach with named cells and named cell ranges. Those are independent from the Sheet name and from the sheet number in the LibreOffice.
REM ***** BASIC *****
Option Explicit
Sub PutDataIntoFirstEmptyRowOfTheTargetRange
Dim oDoc as object
Dim oSourceRange as object
Dim oSourceCell as object
Dim oTargetRange as object
Dim oTargetCell as object
Dim lColNr as long
Dim sFormula as string
oDoc = ThisComponent
oSourceRange = GetNamedRange("mySourceCell", oDoc)
oSourceCell = oSourceRange.ReferredCells.getCellByPosition(0,0)
sFormula = oSourceCell.Formula
oTargetRange = GetNamedRange("myTargetRange", oDoc)
lColNr = 0 'zero based numbering
oTargetCell = GetFirstEmptyCellInAColumnOfRange(oTargetRange, lColNr)
oTargetCell.Formula = sFormula
oSourceCell.Formula = ""
end Sub
'_____________________________________________________________________________________________
Function GetNamedRange(sRange_name as string, optional oCalc_File as object) as object
Dim oDoc, oRange as object
If IsMissing(oCalc_File) then
oDoc = ThisComponent
else
oDoc = oCalc_file
end if
If len(sRange_name) = 0 or sRange_name = "" then
GetNamedRange = NOTHING
Exit function
End if
if oDoc.namedranges.hasByName(sRange_name) then
oRange = oDoc.NamedRanges.getByName(sRange_name)
GetNamedRange = oRange
else
GetNamedRange = NOTHING
end if
end function
'_____________________________________________________________________________________________
Function GetFirstEmptyCellInAColumnOfRange(oTheRange as object, lColNr as long) as object
Dim oCells as object
Dim oCell as object
Dim lRowNr as long
Dim i as long
oCells = oTheRange.ReferredCells
lRowNr = oCells.Rows.Count
For i = 0 to lRowNr-1
oCell = oCells.getCellByPosition(lColNr,i)
If oCell.getType() = 0 Then
GetFirstEmptyCellInAColumnOfRange = oCell
Exit function
end if
next i
end function
'_____________________________________________________________________________________________
I just had the chance to take a peek at this and this might work the best. I was able to see where you named the selection cell and the range. I should be able to work with this. I may try and get this implemented this Saturday after work (overtime for 5 hrs this weekend). I will let you know how it works out and if I have any issues. Thank you very much for this.
I actually checked and tried it last night and it works just fine. The only difference is I had to set the button activation method under the Events tab to “Execute Action” as it would throw an error if I used “When mouse button pressed”.
It’s really a simple thing. I can’t understand why the colleague is on strike. The only problem I see is that we probably didn’t understand the work order properly. Apologies! As soon as you have studied my solution, you will surely agree, and see that I did it within a few minutes.
However, I would recommend to work with a ComboBox instead of “>Data>Validity”.
See attached example. disask95924simpleMagicTrick.ods (24.8 KB)
That’s interesting! I will have a look to see if I can figure this out when I get the chance. It might not be until tomorrow or later in the week that I get the chance to look at it. Thanks so far!
I was finally able to look at it this evening. First thing I noticed is that it works automatically upon making a selection in the list, which does not work for my idea. I do not want it to add the selection to the range unless I activate the macro. The second thing I noticed is that I am unable to tell how your code identifies the list and selection or how it knows what cells to put that data in. My list and the corresponding range that it will fill have moved from their original spots because I am trying to reorganize the sheet and probably will do so again.
The connection to a source range for the listed data, and the bound cell
where the chosen string goes to, are features of the ListBox. Edit it, and you will see.
The range where the history shall be created is given (as a range name) via the .Tag property of the ListBox object which is presented a “Additional Information” in the editor.
I added two solutions for your wish to have a “choice if the choice” shall go to the history.
The code behind is rather raw. One solution uses a hyperlink created with the HYPERLINK() function to call the respective Sub, the pther one now actually has a Button for this.
Be aware of the fact that addresses used in FormControl objects can’t automatically adapt to movements.
