[Solved] So far so good but where next?

Creating a macro - Writing a Script - Using the API

[Solved] So far so good but where next?

Postby barnabyuk » Mon Jan 14, 2019 10:18 pm

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?

Postby barnabyuk » Mon Jan 14, 2019 11:02 pm

Stripped all the whistle and bells down to bare bones to get it to upload
Attachments
One Then stripped.ods
(104.39 KiB) Downloaded 43 times
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?

Postby JeJe » Tue Jan 15, 2019 12:07 am

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?
Openoffice 4.1.2
Windows 8
JeJe
Volunteer
 
Posts: 508
Joined: Wed Mar 09, 2016 2:40 pm

Re: So far so good but where next?

Postby barnabyuk » Tue Jan 15, 2019 12:25 am

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
barnabyuk
 
Posts: 13
Joined: Fri Jan 04, 2019 4:13 pm

Re: So far so good but where next?

Postby JeJe » Tue Jan 15, 2019 12:46 am

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   Expand viewCollapse view

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
Openoffice 4.1.2
Windows 8
JeJe
Volunteer
 
Posts: 508
Joined: Wed Mar 09, 2016 2:40 pm

Re: So far so good but where next?

Postby barnabyuk » Tue Jan 15, 2019 12:54 am

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
barnabyuk
 
Posts: 13
Joined: Fri Jan 04, 2019 4:13 pm

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

Postby JeJe » Tue Jan 15, 2019 1:40 am

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   Expand viewCollapse view

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   Expand viewCollapse view

sub resetBidrow()
bidrow=  doc.CurrentSelection.getRangeAddress.startrow
end sub
Openoffice 4.1.2
Windows 8
JeJe
Volunteer
 
Posts: 508
Joined: Wed Mar 09, 2016 2:40 pm

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

Postby JeJe » Tue Jan 15, 2019 2:14 am

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 41 times
Openoffice 4.1.2
Windows 8
JeJe
Volunteer
 
Posts: 508
Joined: Wed Mar 09, 2016 2:40 pm

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

Postby barnabyuk » Tue Jan 15, 2019 4:07 pm

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?

Postby barnabyuk » Tue Jan 15, 2019 9:58 pm

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
barnabyuk
 
Posts: 13
Joined: Fri Jan 04, 2019 4:13 pm

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

Postby JeJe » Tue Jan 15, 2019 11:02 pm

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.
Openoffice 4.1.2
Windows 8
JeJe
Volunteer
 
Posts: 508
Joined: Wed Mar 09, 2016 2:40 pm

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

Postby barnabyuk » Tue Jan 15, 2019 11:07 pm

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
barnabyuk
 
Posts: 13
Joined: Fri Jan 04, 2019 4:13 pm

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

Postby JeJe » Tue Jan 15, 2019 11:23 pm

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?
Openoffice 4.1.2
Windows 8
JeJe
Volunteer
 
Posts: 508
Joined: Wed Mar 09, 2016 2:40 pm

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

Postby barnabyuk » Tue Jan 15, 2019 11:26 pm

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
barnabyuk
 
Posts: 13
Joined: Fri Jan 04, 2019 4:13 pm

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

Postby JeJe » Tue Jan 15, 2019 11:36 pm

If you moved the module and moved the dialog as well and they're both in a library called "Standard" then it should work.
Openoffice 4.1.2
Windows 8
JeJe
Volunteer
 
Posts: 508
Joined: Wed Mar 09, 2016 2:40 pm

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

Postby barnabyuk » Tue Jan 15, 2019 11:38 pm

Dragged module2 across...Not seen something called dialog though Jeje
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?

Postby JeJe » Tue Jan 15, 2019 11:43 pm

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.
Openoffice 4.1.2
Windows 8
JeJe
Volunteer
 
Posts: 508
Joined: Wed Mar 09, 2016 2:40 pm

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

Postby barnabyuk » Wed Jan 16, 2019 5:07 pm

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 44 times
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?

Postby JeJe » Wed Jan 16, 2019 8:25 pm

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 33 times
Openoffice 4.1.2
Windows 8
JeJe
Volunteer
 
Posts: 508
Joined: Wed Mar 09, 2016 2:40 pm

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

Postby barnabyuk » Wed Jan 16, 2019 10:30 pm

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?

Postby barnabyuk » Thu Mar 14, 2019 4:36 pm

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 23 times
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?

Postby JeJe » Thu Mar 14, 2019 8:16 pm

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   Expand viewCollapse view

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
Openoffice 4.1.2
Windows 8
JeJe
Volunteer
 
Posts: 508
Joined: Wed Mar 09, 2016 2:40 pm

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

Postby JeJe » Thu Mar 14, 2019 8:54 pm

you get set the contents of a cell with this:

Code: Select all   Expand viewCollapse view
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   Expand viewCollapse view
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   Expand viewCollapse view
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   Expand viewCollapse view
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
Openoffice 4.1.2
Windows 8
JeJe
Volunteer
 
Posts: 508
Joined: Wed Mar 09, 2016 2:40 pm

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

Postby barnabyuk » Thu Mar 14, 2019 10:42 pm

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
barnabyuk
 
Posts: 13
Joined: Fri Jan 04, 2019 4:13 pm

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

Postby JeJe » Thu Mar 14, 2019 11:14 pm

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)
Openoffice 4.1.2
Windows 8
JeJe
Volunteer
 
Posts: 508
Joined: Wed Mar 09, 2016 2:40 pm

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

Postby JeJe » Fri Mar 15, 2019 12:06 am

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   Expand viewCollapse view

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
Openoffice 4.1.2
Windows 8
JeJe
Volunteer
 
Posts: 508
Joined: Wed Mar 09, 2016 2:40 pm


Return to Macros and UNO API

Who is online

Users browsing this forum: BenDev, RPG and 5 guests