| View previous topic :: View next topic |
| Author |
Message |
fuel925
General User
Joined: 24 Sep 2007
Posts: 7
|
 Posted: Thu Sep 27, 2007 1:04 am Post subject: Need help using a button please! |
 |
|
Hi there, in a nutshell what I want to do is when I click a button (that I have created using form controls) for the information is certain cells to be copied into cells onto the next sheet.
For example:
I have name in cell A1, date in cell A2 and fee in cell A3. When I click the button, I want that information to be pasted into cells A1, A2 and A3 of the second sheet. But then, when I enter new information into the cells in sheet 1, and press the button a second time, I want the information to be copied into the next row down on sheet 2, and so on. I have recorded a macro, but it just pastes into the same cells on sheet 2 every time, but I want it to paste into the next row every time the button is pressed. Can anyone please tell me how to do this, thanks  |
|
| Back to top |
|
 |
vitcaro
OOo Advocate
Joined: 20 Feb 2007
Posts: 256
Location: Italy
|
| Posted: Thu Sep 27, 2007 6:39 am Post subject: |
|
|
This is an example on how to have access to the first free row on a sheet.
I hope can be of help
| Code: | sub Main
oDoc = ThisComponent
oSheets = oDoc.getSheets
oSheet = oSheets.getByIndex(1)
oCell = oSheet.getCellByPosition(0, 0)
oCellCursor = oSheet.createCursorByRange(oCell)
oCellCursor.gotoEndOfUsedArea False
RangeAddr = oCellCursor.getRangeAddress
row = RangeAddr.EndRow+1
oCell = oSheet.getCellByPosition(0, row)
oCell.setString "NEW ROW"
end sub
|
|
|
| Back to top |
|
|
fuel925
General User
Joined: 24 Sep 2007
Posts: 7
|
| Posted: Thu Sep 27, 2007 11:30 am Post subject: |
|
|
Thanks for the reply! I'm completely new to open office, spreadsheets and any kind of programming, so could you tell me what I need to do with that code to make it work?  |
|
| Back to top |
|
|
vitcaro
OOo Advocate
Joined: 20 Feb 2007
Posts: 256
Location: Italy
|
| Posted: Thu Sep 27, 2007 11:41 am Post subject: |
|
|
Hi fuel925
Send here a post reply with your actual code. Then I will try to suggest the modifications.
bie bie
vitcaro |
|
| Back to top |
|
|
uros
Super User
Joined: 22 May 2003
Posts: 601
Location: Slovenia
|
| Posted: Fri Sep 28, 2007 3:50 am Post subject: |
|
|
| fuel925 wrote: | | Thanks for the reply! I'm completely new to open office, spreadsheets and any kind of programming, so could you tell me what I need to do with that code to make it work? |
A) First you have to know if this macro will be used in this one spreadsheet only or in more then one . Let's say for start to use it in one spreadsheet only.
1. Open this spreadsheet.
2. Goto menu Tools > Macros > Organize macros > OpenOffice.org macros
3. In window Macros from select your file and under it libraray Standard
4. Push button New and then button OK (of course module name can be changed)
5. Copy code listed bellow to main window (usually there is | Code: | REM ***** BASIC *****
Sub Main
End Sub |
- clear it)
5. Save file
B) Then you have to make push button. I use localized version of OOo so my translations could be slightly different!
1. View > Toolbars > Form controls
2. Make push button control
3. Right click on the button
4. Controls > Events
5. Click on button next to "Mouse button released"
6. Aply (connect to,...) Macro:
7. Select your file, Standard, Module1, then in right window select your macro; OK
8. On Form controls toolbar toggle from design to "use" mode.
Click on new button and it should do the job!
Code:
| Code: | Sub Main
oDoc = ThisComponent
oSheets = oDoc.getSheets
oSheet = oSheets.getByIndex(0)
sName = oSheet.getCellByPosition(0, 0).String
dDate = oSheet.getCellByPosition(1, 0).Value
nFee = oSheet.getCellByPosition(2, 0).Value
oSheet = oSheets.getByIndex(1)
oCell = oSheet.getCellByPosition(0, 0)
oCellCursor = oSheet.createCursorByRange(oCell)
oCellCursor.gotoEndOfUsedArea False
RangeAddr = oCellCursor.getRangeAddress
nRow = RangeAddr.EndRow+1
oSheet.getCellByPosition(0, nRow).String = sName
oSheet.getCellByPosition(1, nRow).Value = dDate
oSheet.getCellByPosition(2, nRow).Value = nFee
End Sub |
Hope it helps!
Uros |
|
| Back to top |
|
|
fuel925
General User
Joined: 24 Sep 2007
Posts: 7
|
| Posted: Fri Sep 28, 2007 4:40 am Post subject: |
|
|
Extremely helpful guys, thank you very much! Could you please tell me now which parts of that code I can modify in order to customise it to my needs? For instance, I might want it to paste the data from cells D4, D5, D6 and D7, instead of A1, A2 and A3. How do I modify the code for this? Also, can I make the macro paste from a "date" form control? (see picture)
 |
|
| Back to top |
|
|
fuel925
General User
Joined: 24 Sep 2007
Posts: 7
|
| Posted: Fri Sep 28, 2007 10:36 am Post subject: |
|
|
| Ok, after lots of playing around with the macro code you supplied, I have managed to get the macro to get the information from the cells I want, instead of A1, A2 and A3. However, I still don't know how to get the macro to get the information from the "date control". When I click the cell, it opens a calendar where I can select the date I want, I want to also be able to paste this data into the next sheet. |
|
| Back to top |
|
|
fuel925
General User
Joined: 24 Sep 2007
Posts: 7
|
| Posted: Sun Sep 30, 2007 8:28 am Post subject: |
|
|
| Still need some help with the date problem if possible please! |
|
| Back to top |
|
|
uros
Super User
Joined: 22 May 2003
Posts: 601
Location: Slovenia
|
| Posted: Mon Oct 01, 2007 3:45 am Post subject: |
|
|
Hi fuel925!
Sorry for late reply, I was away for a weekend.
You already have date field there. Attach this code to its "Loose focus" event (or something like that) ...
| Code: | Sub DateField
oDoc = ThisComponent
oSheets = oDoc.getSheets
oSheet = oSheets.getByIndex(0)
oForm = oSheet.DrawPage.Forms.getByIndex(0)
'xray oForm
oField = oForm.getByName("DateField")
sData = oField.Text
dData = DateValue(sData)
oSheet.getCellByPosition(1, 0).Value = dData
End Sub |
Download X-Ray tool, it is simply "must have"!
Uros |
|
| Back to top |
|
|
|
