cell object from string "sheet.cell" reference

[LethalCorpse]

I do feel fairly helpless posting a question like this, because I'm a programmer and it irks me when I don't automatically know how to use a new language. But I've searched google and this forum and a number of other places and can't seem to find any references to what I'm hoping to achieve - I suspect because I can't come up with the right search terms, not because the information isn't out there. Ordinarily I'd learn how to write macros from top to bottom, but I've been given two days to write this spreadsheet and can't afford to take that time. So I'll just go ahead and post the question and hope I don't get flamed too badly.

I've got a spreadsheet which takes time values in 24hr format as data entry. At present it stops on data validity if anything other than a 24 hour time is entered. I need to allow the users to enter time as floats, eg 8.5 instead of contorting their fingers to find the colon and write 8:30. Seems like a small difference, but when you've got a few thousand timeslots to enter...

Anyway, what I want it to do is execute a macro on a validity error which checks that the user has entered a float between 0 and 24 and converts it to a time format, eg 8.5 -->8:30, and then dumps that back into the cell which had the validity error. I've managed to make it call a macro using the

[pitonyak]

What have you managed to accomplish so far? My understanding of your problem is that you want to have the following:

User chooses a cell, any cell.
User enters anything into the cell.
The macro is called auto-magically.
The macro inspects the value and then decides to either ignore the value or change it to a time.

If the macro is called auto-magically, then you must use an event listener. have you figured out how to do that yet?

Also, realize that a time value IS stored as a numerical value already, so you might need to use "trickery" to figure out what was actually entered.
_________________
--
Andrew Pitonyak
My Document: http://www.pitonyak.org/AndrewMacro.odt
Free Info: http://www.pitonyak.org/oo.php
Most hated bug: http://www.openoffice.org/issues/show_bug.cgi?id=84159

[Villeroy]

@Andrew
You don't have to use listeners, when you specify a script event in the validation dialog.
@Lain
I would not write a macro for this task.
Set the validity of the unprotected input-cells to number between 0 and 24.
Then add a column for display of the converted time values.
=Input/24
Format as time.
When I need to write a time-constant into a cell, I use:
dtValue = cDate( dblValue )
oTargetCell.FormulaLocal = cStr(dtValue)
Setting string-property FormulaLocal is like typing the string into the formula bar. This works language independant, BUT the spreadsheet's null-date must be 1899-12-30 which is the default (see Tools>Options>Calc>Calculation>NullDate). StarBasic always uses 1899-12-30 as null-date when you convert a number to date.
_________________
XUbuntu 9.04, OOo 3.1.1(Sun), Sun Java 1.5.0_06

[LethalCorpse]

thanks villeroy, that's very helpful - gets through all the formatting probs. Now all i need to know is how to convert a string "sheet1.a1" reference into a cell object that i can manipulate

[pitonyak]

You probably need to

1. parse the sheet name from the cell name.
2. Get the sheet by name.
3. Get the cell from the sheet by name.

Then again, if you do not really need to obtain the cell, you should be able to use the full name directly if it is used in an equation. Without error checking, it will look something like this:

[Villeroy]

[LethalCorpse]

Okay, I'm almost there guys. Here is my current code:

[Villeroy]

Thank you for using and enhancing my code. Sorry, none of my spreadsheets has spaces in names. I avoid it consequently. So I did not think about this possibility. When you call Function ReplaceHours(sCellValue as String, sTableCell as String) from a validation event, your function needs to return true in order to accept sCellValue. For clarity "sCellValue" should be named "sFormulaLocal" because property FormulaLocal will be set if your validation-function returns true.

[LethalCorpse]

Didn't help. I tried putting it in and outside the if structure, but the value in the cell still stayed the same as what I'd typed.

BTW I've noticed your usage of $ and % signs in the variable names. I first thought this was just naming convention, like putitng s at the start, but then I noticed that you've referred to the same variable with and without the $. Is it just type casting (as in a short form of "As String" or "As Integer") or is it similar to pointers in C?

[LethalCorpse]

No further thoughts on this one? I'm almost over the line, just can't make it dump text into the cell without the typed value overriding. Would it help if I publish the spreadsheet, not jsut the code?

[pitonyak]

What happens if you set a style to the cell for the formatting?
_________________
--
Andrew Pitonyak
My Document: http://www.pitonyak.org/AndrewMacro.odt
Free Info: http://www.pitonyak.org/oo.php
Most hated bug: http://www.openoffice.org/issues/show_bug.cgi?id=84159

[LethalCorpse]

Doesn't do anything I'm afraid. The formatting is all fine - if a date value is typed into the cell, it's formatted correctly. If I hard code the target cell to any other reference than the one which called the macro, it works fine - will take, say, 21.5 entered in one cell, convert that to 21:30, paste 21:30 into the target cell, and it's formatted correctly. But if the target cell is the one that called the macro, it doesn't work. What it appears to be doing is running the macro before it processes my "return" keystroke, or tab, or clicking somewhere else in the spreadsheet. So, my macro takes the entered value runs, and happily sets the cell contents to the changed value. But then, once the macro returns, OOo executes my return keystroke, setting the cell contents to whatever it was I typed. What I need is some way to either prevent it doing this, or do it before the macro runs

[LethalCorpse]

I've played with various return values (true, false, cellvalue etc) but to no avail. The only other way around it that I can see is to have three hidden cells elsewhere in the spreadsheet into which the replacehours macro places the cell location, adjusted cell contents and a change flag. Then I've got listener listening on all cells, which runs when any cell is selected, by mouse click, tab, enter, whatever. The listener checks if the flag has been set, then copies the adjusted cell contents into the cell location and clears the flag. This allows OOo to finish the replacehours macro, store the originally entered text into the cell and move into the next cell before the listener attempts to change the cell contents. Yes, this is incredibly roundabout, but I can't see any way in which to change the cell contents from the replacehours macro - OOo is always going to store the entered value in the cell after the macro returns. Unless anyone has any other suggestions on how to do this, that's what I'll have to do.

[pitonyak]

Can you

1. post the spreadsheet
2. Privide simple instructions on what to do that indicate what happens and what you wanted to happen.

For example:

Place the cursor in sheet1 cell B2
Enter the value 8.5 and press enter

The following happens:

But I wanted the following to happen:
_________________
--
Andrew Pitonyak
My Document: http://www.pitonyak.org/AndrewMacro.odt
Free Info: http://www.pitonyak.org/oo.php
Most hated bug: http://www.openoffice.org/issues/show_bug.cgi?id=84159

[LethalCorpse]

No worries

Place the cursor in any of the white cells in the range A8:AB24.
Type "8:30", or any other time in 24 hour HH:MM format, and the entry should pass the validity test and be stored in the cell.
Type "8.5" or any other decimal value between 0 and 24. The value will fail the validity test, and the "ReplaceHours" macro will run.
After the macro has run, the value in the cell will be "8.5". I want the value in the cell after the macro has run to be the 24 hour representation of the entered value, ie "8.5" --> "8:30", "13.75" --> "13:45"

Using messagebox output it can be shown that the macro runs successfully, converts the cell location string into a cell object, and saves the adjusted value into the cell. It can also be shown that if you hard code a different cell location into the macro in place of the sTableCell which is passed in (I used sTableCell = "'Week 1'.B9") the macro has no problems converting the entered value into a time and saving it into the cell. It can't, however, save anything into the cell which called the validity macro.