Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Perth, Western Australia, Australia
    Posts
    190
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Using same cell formats on user forms (97sr2)

    Hi all,

    I am stumped a bit.

    I am populating a listbox with some values of a workbook, and I want show the values in the list box as they are formatted on the workbook.
    It sounded simple at the start - I thought that NumberFormat would give me the workbook range format for the cells, and then I could use Format(value,format) construct to reapply it to the listbox value.

    Then I found out that NumberFormat could supply a format of 'General' that would then cause Format to fail as it dis not understand.

    Cell("format",CellRef) doesn't help either.

    Is there any way I can capture and use the format that is in effect on the workbook for use on a userform.

    TIA

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using same cell formats on user forms (97sr2)

    The formatted number that shows in a cell can be read using the Text property:
    Activesheet.Range("A1").Text
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Perth, Western Australia, Australia
    Posts
    190
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using same cell formats on user forms (97sr2)

    Thanks Jan Karel,

    It seems so simple when you are shown.

  4. #4
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Perth, Western Australia, Australia
    Posts
    190
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using same cell formats on user forms (97sr2)

    It all seems so simple...

    I was tired last night and omitted to mention that I want to derive some statistics based on workbook values (Maximum, minimum, average etc) and display the results using the same format as the actual value (ie as a formatted date, percentage, currency, custom format or whatever).
    That was why I was trying to 'discover' the workbook format, so that I could apply the same format to the derived value.

    Any ideas would be appreciated.

  5. #5
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using same cell formats on user forms (97sr2)

    OK, what about this then:

    Sub test()
    Dim sFormatstring As String
    sFormatstring = ActiveCell.NumberFormat
    If sFormatstring <> "General" Then
    MsgBox ActiveCell.Value & "---->" & Format(ActiveCell.Value, sFormatstring)
    Else
    'not sure what to do here! maybe get the length of the Text property of the cell?
    'then find out where the decimal separator is and build a new formatstring?
    End If
    End Sub
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  6. #6
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Perth, Western Australia, Australia
    Posts
    190
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using same cell formats on user forms (97sr2)

    Thanks,
    I will give it a try.

    This may be a way to do it with the basic 'general' interpretations of dates, percentages, etc but it will take time to work though the permutations.

    If I succeed I will let you know.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •