Number Format in OWC Spreadsheet

[Don]

Hi,

I need to capture SSN that may begin with 0(zero). For example, if a SSN is
like 012345678, I want to keep the leading 0. But OWC spreadsheet trims the
leading 0 by default. In an MS Excel if I set the cell format to Text, it
works, but OWC spreadsheet doe not supports ‘Text’ format for number. Is
there any workaround on the issue that I am facing? Any help would be really
appreciated.

Thanks,
Don

 

[Don]

Here are some more info.
I use OWC spreadsheet on a client side, IE browser where a user can enter
data. I also use a javascript to manipulate the OWC spreadsheet on a browser.

Don

 

[Josh Sale]

Hi Don.

How about assigning the range of cells to contain SSN's the following
format:
NumberFormat = "000-00-0000"
For example:
oSS.Range("B1").EntireColumn.NumberFormat = "000-00-0000"

josh

 

[Don]

Hi Josh,

Thank you for your response and I'm sorry that I could not reply earlier.
The requirement is that SSN needs to be in a numeric format, e.g. 012345678
w/o hyphon when a user enters it or it is displayed on OWC spreadsheet, and a
leading 0 should not be added when a user enters it less than 9 digits by
mistake.
In the beginning, I used NumberFormat = "000000000", but it caused a
problem, which is adding a leading zero even if a user enters less than 9
digits.

Don

 

[Josh Sale]

Hmmm ... sounds like scope creep to me!

The only way I can think of doing what you describe is to write a Change
event handler that checks if the cell that just changed is in the range that
is suppose to contain SS#'s. When this is the case, the event handler would
then set the changed cell's NumberFormat property to a number of "0"s equal
to the number of digits now in the just changed cell. I suppse the event
handler would need to do something if the user keyed in more than 9 digits.

Happy New Year.

josh

 

[Don]

Hi Josh,

I tried KeyUp, but it captures what was in a cell instead of what a user has
just entered. Also OWC didn't seem to support some events like Focus. I am
not quite clear of how to use or handle OWC event.

Would you show me some snippets of the codes on how to use OWC spreadsheet
event including Change event if you don't mind?

Thanks,
Don

 

[Josh Sale]

Sorry, I don't have a working example of that event in any of my code ...
however the help file offers the following:

Occurs whenever data in one or more cells changes. Both edits and
copy-and-paste operations cause this event to occur.

Private Sub Range_Change( )

Remarks
This event occurs after the EndEdit event; at this point, the data has
already been changed and the change cannot be canceled.

This event requires the WithEvents keyword, so it cannot be used with
VBScript or JScript.

Example
The following example updates a label control on a Visual Basic form when
the value in cell A1 of Sheet1 in Spreadsheet1 changes.

Dim WithEvents rngRange1 As Range

Private Sub Form_Load()

' Set a variable to the range for which you want to capture
' the Change event.
Set rngRange1 = Spreadsheet1.Worksheets("Sheet1").Range("A1")

End Sub

Private Sub rngRange1_Change()
' Change the caption of Label1 to the current value
' of cell A1.
Label1.Caption = rngRange1.Value
End Sub

 

[Don]

Hi Josh,

OWC 11 documents are on C:\Program Files\Common Files\Microsoft Shared\Web
Components\11\1033 if you have installed it.

Spreadsheet1_EndEdit(Accept, FinalValue, Cancel, ErrorDescription) helps.

Thanks,
DOn