Simple Cell Value +1 macro

Hello,

I'm trying to write a basic macro which will increase the value in a cell by 1 when I click on the button I've made. I've searched for hours on the Internet, including this site, and found several websites which give a formula needed but none of them work, I always get an "Object variable not set" error or something about sub-functions which I can't recreate so I can't give the full error message. Sorry.

Based on what I've seen on the Internet, I currently have:

 REM  *****  BASIC  *****

Sub WebsitePlus
ActiveSheet.Range("B18") = ActiveSheet.Range("B18") + 1
End Sub

Where B18 is the cell which currently says 0 and I want to go up by one every time I click the button.

In this macro, the B18 and the 1 are highlighted in red, presumably meaning they're the bits that are wrong. I get a "Basic runtime error: Object variable not set" error when I run it.

Thanks in advance for any help.

hi Barns

Your macro is OK ,can you please place your code between the code tags
[code] code [/code]
What type of button do you have?

Hi Barns
No! It works OK for me. Can you attach a small sample workbook?

Hey Banes

This one is working now .
To have a look
Press the "Alt" key plus the "F11" key

Originally Posted by Barns

With your one I get the error:

A Scripting Framework error occurred while running the Basic script vnd.sun.star.script:Standard.Module1.WebsitePlus?
language=Basic&location=document

Message:The following Basic Script could not be found:
library: 'Standard'
module: 'Module1'
method: 'WebsitePlus'
location: 'document'

So it basically seems as if the macro doesn't exist, no?

Whose code are you referring to,although both work?

Are you using this in Excel?

I've added a macro to increment a specified cell by one. The macro is called using a Command Button from the Controls ToolBox. You can use the same macro & add a button to each tab.

Unfortunately OpenOffice VBA is different to MS Excel's.

In my code using the button you specify the cell within the brackets - in the example Cells(18,2) is B18

Option Explicit

Private Sub CommandButton1_Click()
'use the button to call the macro,specify the cell in the brackets
IncrementCell (Cells(18, 2))
End Sub

The main code takes the cell from the calling code & will increment B18

Option Explicit

Sub IncrementCell(r As Range)
r.Value = r.Value + 1
End Sub

Not sure what changes you need for OpenOffice though

Using Google I found this site

http://wiki.services.openoffice.org/...lls_and_Ranges

As I've noticed before the code looks way more complicated, this adaption might work.

Dim Doc As Object
Dim Sheet As Object
Dim Cell As Object   
 
Doc = ThisComponent
Sheet = Doc.Sheets(0)
 
Cell = Sheet.getCellByPosition(0, 0)
Cell.Value = Cell.Value+1

Try it and let me know

How about
http://user.services.openoffice.org/...forum.php?f=45

Looks like OO references aren't as intuitive as Excel's - Rows & Columns start at 1

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save