+ Reply to Thread
Results 1 to 16 of 16

Lost formatting

  1. #1
    Registered User
    Join Date
    11-17-2006
    Posts
    12

    Lost formatting

    I have a workbook that is used by others and has been working reasonably well for several years. The users are youngsters who tend to play with it when a bit bored but I usually manage to put things right. They seem to have done something special this time.

    The formatting for dates, time and money has gone and I cannot correct it. I have tried the usual formatting of cells, painting formats from other sheets, copying and pasting formats all to no avail. The whole sheet is effected so that new entries appear the same way although the correct format appears in the formula bar.

    I don't know if this is relevant but the sheet relies on VBA form inputs. The code is password protected and only I know the password.

    The PC is not connect to the internet or a network so I have ruled out a virus. (As far as I know, no floppies or USB sticks have been used on it either.)

    Any ideas please?

  2. #2
    Forum Expert oldchippy's Avatar
    Join Date
    02-14-2005
    Location
    Worcester, UK
    MS-Off Ver
    Excel 2007 (Home)
    Posts
    7,099
    Have they applied conditional formatting to the cells?
    oldchippy
    -------------


    Blessed are those who can give without remembering and take without forgetting

    If you are happy with the help you have received, please click the <--- STAR icon on the left - Thanks.

    Click here >>> Top Excel links for beginners to Experts

    Forum Rules >>>Please don't forget to read these

  3. #3
    Registered User
    Join Date
    11-17-2006
    Posts
    12
    No conditional formatting.

  4. #4
    Registered User
    Join Date
    11-17-2006
    Posts
    12
    I've just found that I can change the format of a cell but that does not change what appears in a cell. EG, if I change the cell format from time to a number then click on the cell it is unchanged but in the formula bar does.

    I have also tried a formula but only the formula is displayed. EG, I type "=1+1" and that's what is displayed in the cell rather than "2"

  5. #5
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,655
    I have also tried a formula but only the formula is displayed. EG, I type "=1+1" and that's what is displayed in the cell rather than "2"
    Are the cells formatted as Text?
    Entia non sunt multiplicanda sine necessitate

  6. #6
    Registered User
    Join Date
    11-17-2006
    Posts
    12
    The cells are correctly formatted as date, time, number as appropriate but the formats are not being displayed. IE, "=1+1" displays in a number formatted cell as "=1+1" and not 2.

  7. #7
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,655
    Can you post an example?

  8. #8
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320
    tools/options/view uncheck formulas?

  9. #9
    Registered User
    Join Date
    11-17-2006
    Posts
    12
    Quote Originally Posted by shg View Post
    Can you post an example?
    The formula bar displays "19:31:42" the cell displays "0.813680555555556". The cell is formatted as time.

  10. #10
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,655
    A workbook.

  11. #11
    Registered User
    Join Date
    11-17-2006
    Posts
    12

    Thumbs up

    Quote Originally Posted by martindwilson View Post
    tools/options/view uncheck formulas?
    Thank you. Problem solved.

  12. #12
    Registered User
    Join Date
    11-17-2006
    Posts
    12
    A supplementary question if I may. The workbook is supposed to rely entirely on input by VBA forms. The worksheet is protected with code that un-protects/protects the sheet during any transaction. The sheet can of course be easily unprotected at any time by anyone with a very basic knowledge of Excel.

    Can the sheet be password protected so that users can only use the forms. IE, stop them interferring with data already on the sheet?

  13. #13
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,655
    You could modify the code to trap all changes to the sheet that are not made by code, and to close the workbook with a stern message if it happens.

  14. #14
    Registered User
    Join Date
    11-17-2006
    Posts
    12
    That sounds good. Any iea of the code to use?

  15. #15
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,655
    Right now you have code that transfer data to the worksheet in some fashion. Turn events off before doing so, and turn them on afterward.

    Then, anything that triggers the change event is due to a user that has unprotected the sheet.

  16. #16
    Registered User
    Join Date
    11-17-2006
    Posts
    12
    Thanks for that. I'll have a go at it.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1