[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: 9
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 4 times
Apache Open Office 4.1.5 on Windows 10
barnabyuk
 
Posts: 9
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
 
Posts: 331
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: 9
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
 
Posts: 331
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: 9
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
 
Posts: 331
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 5 times
Openoffice 4.1.2
Windows 8
JeJe
 
Posts: 331
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: 9
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: 9
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
 
Posts: 331
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: 9
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
 
Posts: 331
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: 9
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
 
Posts: 331
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: 9
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
 
Posts: 331
Joined: Wed Mar 09, 2016 2:40 pm


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 3 guests