Posts by rollis13

    Found this snippet in my 'desk drawer' and I think it's what you need:

    That's because many of your cells in column A of sheet MOOZ.A are formatted as text. If your option is activated in most cells you will see a little green triangle on top left and if you select the cell you will see a small yellow popup describing what Excel considers an error (numeric characters should be numbers). If you select 'Convert to number' and your formula in sheet MOOZ.F will give a result.

    To fix the entire column in MOOR.A select a cell with a triangle, press Ctrl+Space to highlight the cells, on top of the column you will find the popup where you can choose 'Convert to number'.

    I will have to try and find a work around to use the keypad

    I think it will be useless if you have a Windows x64, the NumLock bug I indicated will 'get you trouble'.

    This is a long time issue so I don't understand why it never has been fixed. Don't Win x64 users use SendKeys anymore ?

    Hi to all.

    hamptongolfer11, have a try with what I came up.

    I'm using function OnKeys to launch a macro to show the key pressed and add a Tab key. This macro is only for 0 to 9, nothing to do with Numeric keypad since they have a different coding and to all the other keys of the keyboard. If needed you can implement them (and fix any other bug :P).

    To move through the cells the best I could do was to lock the other cells and then lock the sheet. Doing so there would be no reason to use "If Not Intersect..." in the event Worksheet_SelectionChange but since it's a couple of hours I'm banging my head there it is and there it remains ||.

    There also is a problem with NumLock key but that's a Windows x64 problem, you can see my workaround.

    So, macro Worksheet_SelectionChange in the module of the sheet and the other macros in a standard module:


    The code was written to be used in a Form with Textboxes, not for a Double-Click but I'm sure that if you do these changes you should be able to use it with an Activecell input.

    Open the code pane of the Form named frm_Cal and then from the 'Edit' menu choose 'Replace...' and input as 'Search:'

    Me.TextBox1.Value

    and input as 'Replace With:'

    Activecell

    be sure that the only checked is 'Current Module' then click 'Replace All'; you should get n.42 substitutions made.

    That's all, enjoy.

    Paste this code in the Sheet1's module and it will call the calender when double-clicking in column A:

    Code
    Option Explicit
    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
        If Not Intersect(Target, Range("A:A")) Is Nothing Then
            Cancel = True
            frm_Cal.Show
        End If
    End Sub

    As said in post #4 you need to change 3 lines of your code to manage a different column or more than one.

    Code
    If Target.Column <> 1 And Target.Column <> 5 Then Exit Sub
    ...
    XDD = Cells(Target.Row + XD - 1, Target.Column).Value
    ...
    Cells(Target.Row + XD - 1, Target.Column).Value = Replace(Cells(Target.Row + XD - 1, Target.Column), krt, "")

    Since I said "1 = A" then "5 = E" so these lines of code become:

    Code
    If Target.Column <> 5 Then Exit Sub
    ...
    XDD = Cells(Target.Row + XD - 1, 5).Value
    ...
    Cells(Target.Row + XD - 1, 5).Value = Replace(Cells(Target.Row + XD - 1, 5), krt, "")

    If you use On Error Resume Nextyou must be very carefull with your spelling.

    Have you tried:

    Code
    .SpecialCells(xlCellTypeBlanks).EntireRow.Delete

    instead of:

    Code
    .SpecialCells(xlCellTypeBlanks).EntierRow.Delete

    Make these two changes I marked and then move the macro to the ThisWorkBook module. It will then work on every sheet.

    Have a try with this macro to be copied into the ThisWorkbook module. Every time you open the workbook it will check column B and D; if B is checked (1) and D is major than Today it will delete the row.

    Copy this macro in the sheets vbe module and give it a try: