none
Cell formating RRS feed

  • Question

  • I guess this is a bit strange however I want a cell to change in time format once the value was entered.

    Example:

    in A1 i enter 115 and after i press enter the cell must display as follow 1:15.

    Is that possible??


    Thanks you very much

    Wednesday, August 29, 2012 8:53 AM

Answers

    • Marked as answer by HiltonMiles Thursday, August 30, 2012 6:18 PM
    Wednesday, August 29, 2012 9:32 AM
  • I want the msgbox to come up only if the user enter the wrong value not when you delete the cell?

    In the code behind the Cancel button, insert the following line at the beginning:

        Application.EnableEvents = False

    and at the end

        Application.EnableEvents = True


    Regards, Hans Vogelaar

    • Marked as answer by HiltonMiles Friday, August 31, 2012 10:32 AM
    Friday, August 31, 2012 8:54 AM
  • This is a VBA code that also activate the msgbox about the wrong value entered.

    Same answer: also insert lines

        Application.EnableEvents = False

    at the beginning and

        Application.EnableEvents = False

    at the end.


    Regards, Hans Vogelaar

    • Marked as answer by HiltonMiles Friday, August 31, 2012 10:32 AM
    Friday, August 31, 2012 8:56 AM

All replies

    • Marked as answer by HiltonMiles Thursday, August 30, 2012 6:18 PM
    Wednesday, August 29, 2012 9:32 AM
  • Thank you very much it is working 101%

    Thanks you very much

    Thursday, August 30, 2012 6:16 PM
  • I only want the user to insert four values at a time can you advise me how to amend the VBA code?

    Thanks you very much

    Thursday, August 30, 2012 6:43 PM
  • I want this VBA code to work on two different cells separately can you please advise me how to do that?

    If I copy and paste the code twice with different cells it gives me an error?

    Please advise?


    Thanks you very much

    Thursday, August 30, 2012 7:15 PM
  • The code by Chip Pearson contains these lines:

    If Application.Intersect(Target, Range("A1:A10")) Is Nothing Then
        Exit Sub
    End If

    This specifies that you only want to use cells A1, A2, ..., A10 for time entry. If you want to use non-adjacent cells, for example B2 and B5, you can change the above lines to

    If Application.Intersect(Target, Range("B2,B5")) Is Nothing Then
        Exit Sub
    End If

    Note that there is a comma between the two cell addresses, not a colon.

    Regards, Hans Vogelaar

    Thursday, August 30, 2012 7:45 PM
  • What do you mean by "four values" - only four digits? Or something else?

    Regards, Hans Vogelaar

    Thursday, August 30, 2012 7:46 PM
  • Mr Hans,

    I manage to sort out the four digits issue... Thanks.

    As for my previous message regarding the two different cells.

    I want the vba to give me a msgbox when entering data in cell I14 then return to the cell so that the user can correct the data. That I can do however i need another vba for another cell M14 to give me a msgbox and return to the cell so that the user can correct the data entered.

    When I add the following to the VBA code

    Range("I14").Select it will go to that cell even if cell M14 was entered incorrectly so therefore i need two separate VBA's with the same function.

    Hope it makes sence??


    Thanks you very much

    Friday, August 31, 2012 6:49 AM
  • Use

    Target.Select

    instead. Target is the cell whose value has been changed.


    Regards, Hans Vogelaar

    Friday, August 31, 2012 8:14 AM
  • Thanks master.

    More help please...

    On the same sheet I have a macro button 'Cancel' once you press the button it selects the data enter in that row and delete the data. If i run that macro then the msgbox comes up 'Incorrect value entered'.

    I want the msgbox to come up only if the user enter the wrong value not when you delete the cell?

    please help?


    Thanks you very much

    Friday, August 31, 2012 8:23 AM
  • This is a VBA code that also activate the msgbox about the wrong value entered.

    Option Explicit

    Private Sub Workbook_Open()
         'Change "Input" to the name of the worksheet you want to
         'opened when opening
        With Worksheets("Input")
            .Activate
            .Range("E14:L14").Select
            Selection.ClearContents
            .Range("E14").Select
            ActiveSheet.Protect, Password:="sdfa"
        Range("E14").Select
        End With
    End Sub


    Thanks you very much

    Friday, August 31, 2012 8:28 AM
  • I want the msgbox to come up only if the user enter the wrong value not when you delete the cell?

    In the code behind the Cancel button, insert the following line at the beginning:

        Application.EnableEvents = False

    and at the end

        Application.EnableEvents = True


    Regards, Hans Vogelaar

    • Marked as answer by HiltonMiles Friday, August 31, 2012 10:32 AM
    Friday, August 31, 2012 8:54 AM
  • This is a VBA code that also activate the msgbox about the wrong value entered.

    Same answer: also insert lines

        Application.EnableEvents = False

    at the beginning and

        Application.EnableEvents = False

    at the end.


    Regards, Hans Vogelaar

    • Marked as answer by HiltonMiles Friday, August 31, 2012 10:32 AM
    Friday, August 31, 2012 8:56 AM
  • It's working very well thank you master

    Thanks you very much

    Friday, August 31, 2012 10:32 AM