[Solved] So far so good but where next?
[Solved] So far so good but where next?
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?
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
Re: So far so good but where next?
Stripped all the whistle and bells down to bare bones to get it to upload
- Attachments
-
- One Then stripped.ods
- (104.39 KiB) Downloaded 235 times
Apache Open Office 4.1.5 on Windows 10
Re: So far so good but where next?
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?
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)
Re: So far so good but where next?
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
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
Re: So far so good but where next?
If you change your code in module2 to this then on each press of the button it moves to the next row.
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
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
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)
Re: So far so good but where next?
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
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
Re: [Solved] So far so good but where next?
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.
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 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)
Re: [Solved] So far so good but where next?
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.
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 248 times
Windows 10, Openoffice 4.1.11, LibreOffice 7.4.0.3 (x64)
Re: [Solved] So far so good but where next?
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
Re: [Solved] So far so good but where next?
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
Re: [Solved] So far so good but where next?
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.
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)
Re: [Solved] So far so good but where next?
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
Re: [Solved] So far so good but where next?
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?
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)
Re: [Solved] So far so good but where next?
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
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
Re: [Solved] So far so good but where next?
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)
Re: [Solved] So far so good but where next?
Dragged module2 across...Not seen something called dialog though Jeje
Apache Open Office 4.1.5 on Windows 10
Re: [Solved] So far so good but where next?
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)
Re: [Solved] So far so good but where next?
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 240 times
Apache Open Office 4.1.5 on Windows 10
Re: [Solved] So far so good but where next?
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.
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 225 times
Windows 10, Openoffice 4.1.11, LibreOffice 7.4.0.3 (x64)
Re: [Solved] So far so good but where next?
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
Re: [Solved] So far so good but where next?
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
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 214 times
Apache Open Office 4.1.5 on Windows 10
Re: [Solved] So far so good but where next?
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...
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)
Re: [Solved] So far so good but where next?
you get set the contents of a cell with this:
So this would make cell 2,4 empty
You can set a number of cells back to nothing by a loop such as
That would make all the cells on the bidrow 3 between 5 and 10 empty again
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
Code: Select all
Thiscomponent.Sheets(0).getCellByPosition(x,y).value = something 'something being 10, 5, "a fish", whatever
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
Code: Select all
bidrow = 3
for x = 5 to 10 step 2
Thiscomponent.Sheets(0).getCellByPosition(x,bidrow).value = 5
next
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)
Re: [Solved] So far so good but where next?
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
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
Re: [Solved] So far so good but where next?
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)
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)
Re: [Solved] So far so good but where next?
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
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)