[Solved] Code a counter button
-
- Posts: 40
- Joined: Wed Aug 31, 2016 2:15 am
[Solved] Code a counter button
I want a button in my spreadsheet. When I click this button, the number in a cell should increase by 1. I have:
Sub Main()
Dim x As Integer
x = Range("A1").Value
Range("A1").Value = x + 1
End Sub
but it is not working. Maybe a semicolon missing? Or totally wrong?
And, if I may, how should I set security settings so that my macros can run?
Sub Main()
Dim x As Integer
x = Range("A1").Value
Range("A1").Value = x + 1
End Sub
but it is not working. Maybe a semicolon missing? Or totally wrong?
And, if I may, how should I set security settings so that my macros can run?
Last edited by Hagar Delest on Wed Mar 22, 2017 9:36 am, edited 1 time in total.
Reason: tagged [Solved].
Reason: tagged [Solved].
Libre Office 5 on Ubuntu 16.04
- MrProgrammer
- Moderator
- Posts: 4905
- Joined: Fri Jun 04, 2010 7:57 pm
- Location: Wisconsin, USA
Re: code a counter button
[Solved] Push Buttons Performing Math in CellsPedroski55 wrote: When I click this button, the number in a cell should increase by 1.
If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.
[Tutorial] Ten concepts that every Calc user should know
Mr. Programmer
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel. The locale for any menus or Calc formulas in my posts is English (USA).
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel. The locale for any menus or Calc formulas in my posts is English (USA).
-
- Posts: 40
- Joined: Wed Aug 31, 2016 2:15 am
Re: code a counter button
Thanks a lot! Contrary to my expectations, I managed to make the spin buttons work!
I would still like to know what is wrong with the above basic code. It works for a friend who uses Excel. He sent me the code!
I get this error: BASIC runtime error.
Sub-procedure or function procedure not defined.
I would still like to know what is wrong with the above basic code. It works for a friend who uses Excel. He sent me the code!
I get this error: BASIC runtime error.
Sub-procedure or function procedure not defined.
Last edited by Pedroski55 on Tue Mar 21, 2017 7:01 am, edited 1 time in total.
Libre Office 5 on Ubuntu 16.04
Re: code a counter button
Maybe it is as simple as VBA and OpenOffice Basic are two different languages with different syntax?
Cheers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.2.2; SlackBuild for 24.2.2 by Eric Hameleers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.2.2; SlackBuild for 24.2.2 by Eric Hameleers
Re: code a counter button
This will increase the current cell's value.
And assuming A1 is on Sheet1 then this will do it.
Code: Select all
Sub PlusOne
oDoc = ThisComponent
oCell = oDoc.getCurrentSelection
oCell.Value = oCell.Value + 1
End Sub
Code: Select all
Sub PlusOne
oDoc = ThisComponent
oSheet = oDoc.getSheets.getByName("Sheet1")
oCell = oSheet.getCellRangeByName("A1")
oCell.Value = oCell.Value + 1
End Sub
Last edited by JohnV on Tue Mar 21, 2017 7:44 am, edited 1 time in total.
-
- Posts: 40
- Joined: Wed Aug 31, 2016 2:15 am
Re: code a counter button
Ok, in OO BASIC how should I define Range("A1").Value = x + 1
@ JohnV: tried your code, get this: BASIC runtime error.
Property or method not found: Value.
@ JohnV: tried your code, get this: BASIC runtime error.
Property or method not found: Value.
Libre Office 5 on Ubuntu 16.04
Re: code a counter button
Have a look at this post which may give you some insight.
Disclaimer; I don't use Basic, but it looks like some of the code in that post is what you want.
Disclaimer; I don't use Basic, but it looks like some of the code in that post is what you want.
Cheers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.2.2; SlackBuild for 24.2.2 by Eric Hameleers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.2.2; SlackBuild for 24.2.2 by Eric Hameleers
Re: code a counter button
The code works for me fine in my LibreOffice 4.4.7. But it will not work if you use it in an .xls file and you can try open it in MS Excel. The MS VBA and the API of the AOO/LO are not compatible. (API = Application Programming Interface. You need call the API function for this task from the StarBasic or from the other supported programming language.)@ JohnV: tried your code, get this: BASIC runtime error.
Property or method not found: Value.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
Re: Code a counter button
Running Linux, you do not need a 300MB office suite to add up numbers in a file:
Save it as ~/bin/counter.sh and make it executable with chmod +x ~/bin/counter.sh.
Start a terminal and run counter.sh
Finish with Ctrl+C or by closing the terminal.
It looks like this:
Code: Select all
#!/bin/bash
COUNTFILE=~/counter.txt
COUNTVAR=0
function finish {
echo $COUNTVAR > $COUNTFILE
echo -e "\nCaught EXIT and saved $COUNTVAR to $COUNTFILE"
}
trap finish EXIT
if [ -f $COUNTFILE ]; then COUNTVAR=`cat $COUNTFILE`; fi
while true; do
echo -e "Add integer to $COUNTVAR [default 1]: \c"
read NUM
if [ -z $NUM ]; then NUM=1; fi
let COUNTVAR+=$NUM
done
Start a terminal and run counter.sh
Finish with Ctrl+C or by closing the terminal.
It looks like this:
Code: Select all
$ counter.sh
Add integer to 129 [default 1]: ads
Add integer to 129 [default 1]: gfds
Add integer to 129 [default 1]:
Add integer to 130 [default 1]:
Add integer to 131 [default 1]: 12
Add integer to 143 [default 1]: ^C
Caught EXIT and saved 143 to /home/username/counter.txt
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
-
- Posts: 40
- Joined: Wed Aug 31, 2016 2:15 am
Re: Code a counter button
Thanks for all the tips!
I also use Libre Office and Ubuntu. I try to avoid anything Microsoft.
Also got a cold so not so fit right now. I'll try all suggestions, see if I can make it work.
My friend uses this counter to add up cups of coffee sold. He and his roommate have started selling coffee on campus from their dorm. He added a button to his Excel file and each time he sells a certain kind of coffee, he clicks the button to keep a record of how much of what he is selling. I thought that is a handy thing, might be useful for me, not for selling coffee though.
I also use Libre Office and Ubuntu. I try to avoid anything Microsoft.
Also got a cold so not so fit right now. I'll try all suggestions, see if I can make it work.
My friend uses this counter to add up cups of coffee sold. He and his roommate have started selling coffee on campus from their dorm. He added a button to his Excel file and each time he sells a certain kind of coffee, he clicks the button to keep a record of how much of what he is selling. I thought that is a handy thing, might be useful for me, not for selling coffee though.
Libre Office 5 on Ubuntu 16.04
-
- Posts: 40
- Joined: Wed Aug 31, 2016 2:15 am
Re: Code a counter button
Thank you all for your patience, I am never going to be a coder! I got it working in the end!
I downloaded the file from Zizi64, and thanks a lot for that! It works great!
Also the 2nd code from JohnV is great and it works! Thanks a lot! Maybe I had a security issue. I have the security set to low, but that's not good.
I guess I just need a list of Open Office Basic commands and syntax, I can learn them slowly. Anyone know a good link?
I downloaded the file from Zizi64, and thanks a lot for that! It works great!
Also the 2nd code from JohnV is great and it works! Thanks a lot! Maybe I had a security issue. I have the security set to low, but that's not good.
I guess I just need a list of Open Office Basic commands and syntax, I can learn them slowly. Anyone know a good link?
Libre Office 5 on Ubuntu 16.04
Re: Code a counter button
It is enough to set the macro security level to Medium. In this case you need to enable (manually) the macros located in a document.Thanks a lot! Maybe I had a security issue. I have the security set to low, but that's not good.
You can store the macros in the Standard directory of the MyMacros in the User profile of the AOO/LO. In this case not needed enable the macros at every launching a file that uses some macros.
These "commands" (functions) are not the parts of the OpenOffice Basic (StarBasic), but they are API functions.I guess I just need a list of Open Office Basic commands and syntax, I can learn them slowly.
You can use the API functions with all of the supported programming languages.
(API - Application Programming Interface)
You must study the API functions, and the usage methods of them.Anyone know a good link?
Andrew Pitonyak's macro books are good starting points for you.
http://www.pitonyak.org/oo.php
http://api.libreoffice.org/
http://www.openoffice.org/api/
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
-
- Posts: 40
- Joined: Wed Aug 31, 2016 2:15 am
Re: [Solved] Code a counter button
OpenOffice BASIC guide linked at
https://wiki.openoffice.org/wiki/Docume ... ASIC_Guide
Entire earlier version of the guide at
https://wiki.openoffice.org/w/images/c/ ... o3.2.0.pdf
Although this is for an earlier version, I think any changes (additions?) are unlikely to be relevant or cause difficulty for most users.
https://wiki.openoffice.org/wiki/Docume ... ASIC_Guide
Entire earlier version of the guide at
https://wiki.openoffice.org/w/images/c/ ... o3.2.0.pdf
Although this is for an earlier version, I think any changes (additions?) are unlikely to be relevant or cause difficulty for most users.
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
Re: [Solved] Code a counter button
My shell script works out of the box with the enter key on your keyboard.
Another solution: http://forum.openoffice.org/en/forum/do ... hp?id=3048 with an input form and a database table. The contained report shows what has been saved when.
You can also put a numeric form control onto a sheet, link a cell to it and use the up/down arrow without any macro.
Another solution: http://forum.openoffice.org/en/forum/do ... hp?id=3048 with an input form and a database table. The contained report shows what has been saved when.
You can also put a numeric form control onto a sheet, link a cell to it and use the up/down arrow without any macro.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: [Solved] Code a counter button
Nother solution without macro.
- Attachments
-
- counter.ods
- (11.37 KiB) Downloaded 235 times
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice