[Solved] So far so good but where next?

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
barnabyuk
Posts: 13
Joined: Fri Jan 04, 2019 4:13 pm

[Solved] So far so good but where next?

Post by barnabyuk »

Hi oh wise ones. Help!

Please bear with me it's a long story.

I started out making a score sheet, (trying to save the world by going paperless), for a game of cards that we pensioners play fairly regularly.

After getting the thing to work I decide to automate as much of the process as possible to try to eliminate fat finger errors.

This is as far as I have got after about two weeks reading and experimenting and stealing bits of code.
The macro called Bids (triggered in the sheet by the 7 Hearts button), works well in that it selects the bid cells at g4, k4, o4, and s4 but I then realised that by doing it the way I was meant I was going to have to make 17 buttons and therefore 17 macros just for the bidding and another 17 buttons and 17 macros for the Won column.

I have searched and looked and even tried to understand Andrew Pitonyak's explanations. Nothing I have found works. At 72 years of age I reckon I'm too old to have to become a professor of macros so I will appreciate it if you can help me to cut all this down to a couple of buttons. Plus if you see other fancy ways to improve the functionality, I assure you I'm not too proud to accept your generous charity. Happy New Year to you all.

PS I am trying to upload the sheet but the site tells me it has to be below 128K any ideas how to show it you guys?
Last edited by barnabyuk on Tue Jan 15, 2019 12:56 am, edited 2 times in total.
Apache Open Office 4.1.5 on Windows 10
barnabyuk
Posts: 13
Joined: Fri Jan 04, 2019 4:13 pm

Re: So far so good but where next?

Post by barnabyuk »

Stripped all the whistle and bells down to bare bones to get it to upload
Attachments
One Then stripped.ods
(104.39 KiB) Downloaded 229 times
Apache Open Office 4.1.5 on Windows 10
JeJe
Volunteer
Posts: 2764
Joined: Wed Mar 09, 2016 2:40 pm

Re: So far so good but where next?

Post by JeJe »

What's the card game called? That might give us a clearer idea of what you're trying to do.

You press a button and the input box pops up 4 times... I presume a number is entered... and the other button is to calculate who's won? Is that who's bid the highest or lowest?
And the same thing happens 17 times over in the rows below?
Windows 10, Openoffice 4.1.11, LibreOffice 7.4.0.3 (x64)
barnabyuk
Posts: 13
Joined: Fri Jan 04, 2019 4:13 pm

Re: So far so good but where next?

Post by barnabyuk »

Hi Jeje. On click (7 Hearts) the four inputs go to G4, K4, O4, and S4 respectively. The player calls number of tricks they think they will win and that info is input via inputbox under the columns called "B". Same will happen with the columns named W for won.
The way I have it at the moment means different buttons and different macros for every line which would make the file massive and messy so I wanted to make it happen using just two buttons. One for the B (bids) and one for the W(Won).
The formulae in the cell themselves do all the actual calculating.
I'm sorry that I haven't got a clue what the game is actually called but years ago we started calling it "One Then" and it stuck.
Hope you can help :)
Apache Open Office 4.1.5 on Windows 10
JeJe
Volunteer
Posts: 2764
Joined: Wed Mar 09, 2016 2:40 pm

Re: So far so good but where next?

Post by JeJe »

If you change your code in module2 to this then on each press of the button it moves to the next row.

Code: Select all


global bidrow

Sub BIDS

Doc = ThisComponent
Sheets = Doc.getSheets  'get the collection of Sheets
Sheet = Sheets.GetByName("Sheet1")  'get the specific Sheet

c= array("g","K","O","S")
for i = 1 to 4
sText = InputBox ("How many bids does PLAYER" & i & "want to make?"
Cell = Sheet.getCellRangeByName(c(i-1) & 4 + bidrow)  'Get the named Cell
Cell.string = sText
next

bidrow = bidrow +1

End Sub

Edit:
I don't know how many rows you have but if its say 17 then to reset to the top row after row 17 change the line
bidrow = bidrow +1
To
if bidrow = 17 then bidrow = 0 else bidrow = bidrow +1
Windows 10, Openoffice 4.1.11, LibreOffice 7.4.0.3 (x64)
barnabyuk
Posts: 13
Joined: Fri Jan 04, 2019 4:13 pm

Re: So far so good but where next?

Post by barnabyuk »

OMG!!!!
PERFECT. You are a star my friend. I presume for the won bids I use the same but alter the range.
Thank you sooooooooooo much!

The people at the card school will be WELL impressed...lol
Apache Open Office 4.1.5 on Windows 10
JeJe
Volunteer
Posts: 2764
Joined: Wed Mar 09, 2016 2:40 pm

Re: [Solved] So far so good but where next?

Post by JeJe »

A potential problem I can see with that code is if you make an error... then you're stuck in the middle of code that's going to keep showing you loads of input boxes. So this variation gives 4 input boxes based on the current row. You'd have to press the down arrow before each button press or press in the row you want with the mouse.

Code: Select all


Sub BIDS

Doc = ThisComponent
Sheets = Doc.getSheets  'get the collection of Sheets
Sheet = Sheets.GetByName("Sheet1")  'get the specific Sheet

curbidrow=  doc.CurrentSelection.getRangeAddress.startrow
c= array("g","K","O","S")
for i = 1 to 4
sText = InputBox ("How many bids does PLAYER" & i & "want to make?"
Cell = Sheet.getCellRangeByName(c(i-1) &  curbidrow+1)  'Get the named Cell
Cell.string = sText
next
End Sub


Another approach would be to keep the first code and have a button that calls this sub to reset the bidrow variable to the current row

Code: Select all


sub resetBidrow()
bidrow=  doc.CurrentSelection.getRangeAddress.startrow
end sub
Windows 10, Openoffice 4.1.11, LibreOffice 7.4.0.3 (x64)
JeJe
Volunteer
Posts: 2764
Joined: Wed Mar 09, 2016 2:40 pm

Re: [Solved] So far so good but where next?

Post by JeJe »

One more thought is if the bidding is always only a single digit then you'd be better with your own dialog box that closes as soon as you press the digit - then you don't have to press the enter key as well. See the attached mod of your document.

The input box is kind of terrible anyway - with your own dialog you can position it so its not over the cells you're working on and make it the size you want etc.
Attachments
One Then stripped.ods
(105.89 KiB) Downloaded 238 times
Windows 10, Openoffice 4.1.11, LibreOffice 7.4.0.3 (x64)
barnabyuk
Posts: 13
Joined: Fri Jan 04, 2019 4:13 pm

Re: [Solved] So far so good but where next?

Post by barnabyuk »

JeJe, you astound me. Not only because of your expertise but also your willingness to help me as much as you have and for that I sincerely thank you. The text box is a fantastic idea and I will incorporate it in the project. Any more suggestions welcome.
Apache Open Office 4.1.5 on Windows 10
barnabyuk
Posts: 13
Joined: Fri Jan 04, 2019 4:13 pm

Re: [Solved] So far so good but where next?

Post by barnabyuk »

Hmm...Dunno what I done wrong JeJe but when I ran it today I get an error on this line of code "LoadDialog() = CreateUNODialog(oLib.GetByName(oDialogName))", saying that "com.sun.star.container NoSuchElementException". Any advice?
Apache Open Office 4.1.5 on Windows 10
JeJe
Volunteer
Posts: 2764
Joined: Wed Mar 09, 2016 2:40 pm

Re: [Solved] So far so good but where next?

Post by JeJe »

Have you changed the name of the library or the name of the dialog or this line at all?

biddlg=loaddialog("Standard","Dialog1")

You can change the name of the Dialog but you've got to change "Dialog1" in that line to the new name as well.
Windows 10, Openoffice 4.1.11, LibreOffice 7.4.0.3 (x64)
barnabyuk
Posts: 13
Joined: Fri Jan 04, 2019 4:13 pm

Re: [Solved] So far so good but where next?

Post by barnabyuk »

Hi Jeje...Sorry to be a pain. I haven't altered anything at all...Just moved it across to my original sheet.
Apache Open Office 4.1.5 on Windows 10
JeJe
Volunteer
Posts: 2764
Joined: Wed Mar 09, 2016 2:40 pm

Re: [Solved] So far so good but where next?

Post by JeJe »

biddlg=loaddialog("Standard","Dialog1") is the line that calls that function.
The error message is saying you don't have a dialog in the "Standard" library with the name "Dialog1".

Did you move or copy the Dialog as well as the code across to the original document?
Windows 10, Openoffice 4.1.11, LibreOffice 7.4.0.3 (x64)
barnabyuk
Posts: 13
Joined: Fri Jan 04, 2019 4:13 pm

Re: [Solved] So far so good but where next?

Post by barnabyuk »

Went to macro organiser and dragged the module to my sheet.
I was just looking if I could have done something wrong and notice that there appears to be a dialog box called dialog1...Dunno what it does
Apache Open Office 4.1.5 on Windows 10
JeJe
Volunteer
Posts: 2764
Joined: Wed Mar 09, 2016 2:40 pm

Re: [Solved] So far so good but where next?

Post by JeJe »

If you moved the module and moved the dialog as well and they're both in a library called "Standard" then it should work.
Windows 10, Openoffice 4.1.11, LibreOffice 7.4.0.3 (x64)
barnabyuk
Posts: 13
Joined: Fri Jan 04, 2019 4:13 pm

Re: [Solved] So far so good but where next?

Post by barnabyuk »

Dragged module2 across...Not seen something called dialog though Jeje
Apache Open Office 4.1.5 on Windows 10
JeJe
Volunteer
Posts: 2764
Joined: Wed Mar 09, 2016 2:40 pm

Re: [Solved] So far so good but where next?

Post by JeJe »

In the basic organiser there's a tab for your modules and one for dialogs - you move the Dialog1 across in the dialog bit the same way you moved module2 across in the modules bit. If Dialog1 has disappeared try downloading the file again and moving it across.
Windows 10, Openoffice 4.1.11, LibreOffice 7.4.0.3 (x64)
barnabyuk
Posts: 13
Joined: Fri Jan 04, 2019 4:13 pm

Re: [Solved] So far so good but where next?

Post by barnabyuk »

I have the "bid" dialog box working perfectly now thanks to your instructions. I have attempted to replicate the code from the macro you supplied to score the Won column next to the Bid column, changing all the "Bid" prefixes to "won" eg: bidrow to wonrow, bidldlg to wondlg etc. Also attempted to create another dialog box (dialog2) with the same dimensions etc but when I run it from the spreadsheet I get an error: "Function DialogInputBox(st as string)" "Incorrect property value". I promise I will stop pestering you after this. "Sad"
Attachments
jeje.txt
(711 Bytes) Downloaded 230 times
Apache Open Office 4.1.5 on Windows 10
JeJe
Volunteer
Posts: 2764
Joined: Wed Mar 09, 2016 2:40 pm

Re: [Solved] So far so good but where next?

Post by JeJe »

I've added a dialog2, put your code in module 3, changed the function to call Dialog2 to DialogInputBox2.

I added the end bracket to ("How many bids does PLAYER" & i & " want to make?") as I missed it out.

can't see where you would get "Incorrect property value"... unless 1350 is too big a number of pixels to position the left of the dialog... doesn't cause a problem on my computer.

It won't be obvious but if you look at the dialogs and double click on the textboxes then a box called properties pops up. On the events tab for the key pressed event I set it to call the function Standard.module2.keypressed.

I can suggest you copy/move module 3 and dialog 2 from my file. If there already is a module3 or dialog2 in the library you'll have to delete those first.
Attachments
One Then stripped.ods
(107.48 KiB) Downloaded 217 times
Windows 10, Openoffice 4.1.11, LibreOffice 7.4.0.3 (x64)
barnabyuk
Posts: 13
Joined: Fri Jan 04, 2019 4:13 pm

Re: [Solved] So far so good but where next?

Post by barnabyuk »

I am exhausted my friend. I have tried every which way I can to get it to work, even using the exact same script ad "Bids" and altering the names. The "Won" button on my sheet works once and thereafter goes into bad with the properties error state. I'm getting a bit desperate now because I don't have that much hair at my age and pulling more out isn't helping. LOL. I have uploaded the sheet to Google drive in case you want to see what I'm doing wrong.(Please do look). the address is https://drive.google.com/file/d/1YXaPXB ... sp=sharing.
Apache Open Office 4.1.5 on Windows 10
barnabyuk
Posts: 13
Joined: Fri Jan 04, 2019 4:13 pm

Re: [Solved] So far so good but where next?

Post by barnabyuk »

Hi JeJe. Long time no see. I am really sorry to bother you again. I have really tried not to.
Thanks to you we have been enjoying the electronic scoring when playing cards at the well being club.
A problem that keeps occurring though is the fat finger syndrome whereby the scorer hits the wrong number when entering bids made and bids won.
I have struggled with this for two weeks now and tried all ways to get the value of bidrow into another macro to delete the line and reset bidrow value.
If any of us old fools make a mistake with the input then we have to revert to pen and paper for the rest of the game.
I have include the original macro that you made for me and the one that I have been working on to try to resolve our issue.
Any help will be much appreciated. :)

Martin
Attachments
Plea for help.txt
(1.89 KiB) Downloaded 209 times
Apache Open Office 4.1.5 on Windows 10
JeJe
Volunteer
Posts: 2764
Joined: Wed Mar 09, 2016 2:40 pm

Re: [Solved] So far so good but where next?

Post by JeJe »

I only vaguely remember what was happening here... but I've dug up the file you sent me.

How about adding a textbox to the page (It will be called "Text Box 1" if its the first one added) and changing the code so bidrow is determined by the value in it using the two added lines below? If there's a mistake you just change the number in the textbox.

If this is no use let me know and I'll have a better look...

Code: Select all


bidrow=thiscomponent.Sheets.GetByName("Sheet1").drawpage.Forms(0).getbyname("Text Box 1").text '*************text box lines
Doc = ThisComponent
Sheets = Doc.getSheets  'get the collection of Sheets
Sheet = Sheets.GetByName("Sheet1")  'get the specific Sheet

c= array("g","K","O","S")
for i = 1 to 4
'sText = InputBox ("How many bids does PLAYER" & i & "want to make?"
sText = DialogInputBox ("How many bids does PLAYER" & i & " want to make?"
Cell = Sheet.getCellRangeByName(c(i-1) & 4 + bidrow)  'Get the named Cell
Cell.string = sText
next

bidrow = bidrow +1
thiscomponent.Sheets.GetByName("Sheet1").drawpage.Forms(0).getbyname("Text Box 1").text =bidrow '*************text box lines
Windows 10, Openoffice 4.1.11, LibreOffice 7.4.0.3 (x64)
JeJe
Volunteer
Posts: 2764
Joined: Wed Mar 09, 2016 2:40 pm

Re: [Solved] So far so good but where next?

Post by JeJe »

you get set the contents of a cell with this:

Code: Select all

Thiscomponent.Sheets(0).getCellByPosition(x,y).value = something 'something being 10, 5, "a fish", whatever
So this would make cell 2,4 empty

Code: Select all

Thiscomponent.Sheets(0).getCellByPosition(2,4).value = ""

You can set a number of cells back to nothing by a loop such as

Code: Select all

bidrow = 3
for x = 5 to 10 
Thiscomponent.Sheets(0).getCellByPosition(x,bidrow).value = ""
next



That would make all the cells on the bidrow 3 between 5 and 10 empty again

Code: Select all

bidrow = 3
for x = 5 to 10 step 2
Thiscomponent.Sheets(0).getCellByPosition(x,bidrow).value = 5
next



The step 2 changes it so alternate cells are stepped over (skipped)
cells 5, 3 then 7,3 then 9,3 are set to the value 5
Windows 10, Openoffice 4.1.11, LibreOffice 7.4.0.3 (x64)
barnabyuk
Posts: 13
Joined: Fri Jan 04, 2019 4:13 pm

Re: [Solved] So far so good but where next?

Post by barnabyuk »

Hi JeJe...Thanks for getting back to me so quick. I tried to upload the .ods to here but it didn't work. I have uploaded the whole thing to GDrive. I hope you can take a look and you will see some of the things
I have worked on in the prog. I am trying to make something that confirms whether the inputs for "Bids" is correct and if not it would reset that row "BidRow" and not move on to the next row.
I keep getting out of range error. By the way, if you have a favourite charity I would happily make a small contribution to them for the invaluable help you have given me thus far.
The document is stored at https://drive.google.com/open?id=1H_Emb ... KLWa_dBGbp
Thanks again
Apache Open Office 4.1.5 on Windows 10
JeJe
Volunteer
Posts: 2764
Joined: Wed Mar 09, 2016 2:40 pm

Re: [Solved] So far so good but where next?

Post by JeJe »

Its no bother at all - I get help here occasionally too when I get stuck. Without all the old threads of questions asked and answered to look at it would be really hard to learn OO.

I managed to download it this time so I'll have a look.

Above, I should have put

Thiscomponent.Sheets(0).getCellByPosition(2,4).string = ""

instead of .value as the string can be empty but not the value. (Thanks to to Villeroy for this)
Windows 10, Openoffice 4.1.11, LibreOffice 7.4.0.3 (x64)
JeJe
Volunteer
Posts: 2764
Joined: Wed Mar 09, 2016 2:40 pm

Re: [Solved] So far so good but where next?

Post by JeJe »

Try this: change module 2 to this

the bid row will appear in cell E2. To got back to a row change E2 to the appropriate value
If you want to undo the last row replace the number in the cell with an x

You could use a textbox instead of a cell as suggested above.
Or add a button that calls the resetrow sub

Code: Select all


global dlgReturn
global bidrow

Sub BIDS

Doc = ThisComponent
Sheets = Doc.getSheets  'get the collection of Sheets
Sheet = Sheets.GetByName("Sheet1")  'get the specific Sheet

if sheet.getCellByPosition(4,1).string = "x" then  '***********ADDED IF CELL = X THEN RESET
resetrow
end if


 bidrow= val(sheet.getCellByPosition(4,1).string) ''***********ADDED SET BID ROW TO VALUE OF CELL 4,1

c= array("g","K","O","S")
for i = 1 to 4
f = i + 5
ocell = sheet.getCellByPosition(1,f)
sText = DialogInputBox ("How many bids does  " & oCell.string & " want to make?")
Cell = Sheet.getCellRangeByName(c(i-1) & 4 + bidrow)  'Get the named Cell
Cell.string = sText
next

bidrow = bidrow +1
 sheet.getCellByPosition(4,1).value = bidrow ''***********ADDED SET VALUE OF CELL 4,1 TO BIDROW

End Sub



Function DialogInputBox(st as string)

biddlg=loaddialog("Standard","Dialog1")
biddlg.getcontrol("Label1").model.label =st
biddlg.setpossize 1150,350,0,0,3
res = biddlg.execute
DialogInputBox=dlgReturn
end function

Function LoadDialog(oLibraryName As String, oDialogName As String) 'just to load dialog
	Dim oLib As Object
	DialogLibraries.LoadLibrary(oLibraryName)
	oLib = DialogLibraries.GetByName(oLibraryName)
	LoadDialog() =  CreateUNODialog(oLib.GetByName(oDialogName))
End Function

sub keypressed(oEvt)

dlgreturn = oEvt.source.model.text
oEvt.source.context.endexecute

end sub

sub resetrow()'***********ADDED SUB ADAPTS CODE ABOVE TO CLEAR LAST BIDROW CELLS

bidrow = bidrow -1

Doc = ThisComponent
Sheets = Doc.getSheets  'get the collection of Sheets
Sheet = Sheets.GetByName("Sheet1")  'get the specific Sheet
c= array("g","K","O","S")
for i = 1 to 4
f = i + 5
ocell = sheet.getCellByPosition(1,f)
Cell = Sheet.getCellRangeByName(c(i-1) & 4 + bidrow)  'Get the named Cell
Cell.string = ""
next


sheet.getCellByPosition(4,1).value=bidrow
end sub
Windows 10, Openoffice 4.1.11, LibreOffice 7.4.0.3 (x64)
Post Reply