Posts by Kneejerk

  • Force A Date Format

    Re: Force A Date Format


    NP. You'll probably find out that it's down to Excel 'deciding' what you want the date to be rather than you being able to force it i.e. if you enter 12/01/2007 for January 12th it'll probably decide that you want 01/12/2007 (1st Dec).


    Date/Time formats are still one of the most infuriating parts of Excel (although not necessarily a fault of the program).


    KJ

  • Force A Date Format

    Re: Force A Date Format


    What error (if any) do you get? Seems to work okay for me, but there will likely be issues with Excel and it's Date/Time 'foibles'.


    KJ

  • Paste Special When Recording

    Re: Paste Special When Recording


    Rather than select and paste try using


    Code
    Dim f As Range
Dim colf As Range
Set colf = Range("F1:F65536")
For Each f In colf
f.Offset(0, -1).Value = f.Value
f.ClearContents
Next f


    You can then put other checks on it to stop it from check once the last value in the column has been reached.


    HTH


    KJ

  • Custom Date Format To Be Retained In .csv Also

    Re: Custom Date Format To Be Retained In .csv Also


    Have you tried switching the lines around? i.e.


    Code
    dtMydate = Format(strWhen, "yyyy-mm-dd hh:mm:ss")
dtMydate = CDate(dtMydate)


    Haven't tested it myself, but I know the problems encountered with Date/Time format in Excel. Got to be worth a punt? ;)


    KJ

  • Application-defined Or Object-defined Error

    Re: Application-defined Or Object-defined Error


    Works for me (albeit slightly butchered to remove uneeded code). Are you sure 'Sheet1' exists as that i.e. you haven't renamed another sheet to Sheet1 have you?


    Out of curiosity, why do you bother with selecting "D15" on the added sheet and then select the activecell again after naming the sheet?


    KJ

  • Cell Format Dependant On Rows

    Re: Cell Format Dependant On Rows


    I'm assuming you would want them to be the same colour as specific other cells in the row i.e. If A1 is 'None' and G1 is Yellow then you want A1 to be yellow, whereas if G1 is white then you want A1 to be white.


    Would that be a correct assumption? If not then if you could provide a example that may help me get my head around it. :)


    KJ

  • Double Click Hyperlink, Don't Folow On Single Click

    Re: Double Click Hyperlink, Don't Folow On Single Click


    I agree, I don't think it's possible. Every reference for Hyperlinks in Excel suggest that you can select the cell by holding the mouse button down for a time. Nothing suggests an alternative approach.


    Quote

    To select a cell that has a hyperlink in it without jumping to the hyperlink destination, click the cell and hold the mouse button until the cursor becomes a cross , then release the mouse button.


    KJ

  • paste from workbook to workbook

    Re: paste from workbook to workbook


    Use



    HTH


    EDIT: Got distracted while testing, God I was slow!! :)


    KJ

  • Auto Lock & Protect Cells After Entry

    Re: Lock Cell After Entry


    Well, if the workbook is protected then you can lock cells by using


    Code
    Function InRange(Range1 As Range, Range2 As Range) As Boolean
'returns True if Range1 is within Range2
Dim InterSectRange As Range
    Set InterSectRange = Application.Intersect(Range1, Range2)
    InRange = Not InterSectRange Is Nothing
    Set InterSectRange = Nothing
End Function


    This allows you to specify a range in the below.


    Code
    Private Sub Worksheet_Change(ByVal Target As Range)
If InRange(ActiveCell, Range("B1:B100")) Then
ActiveSheet.Unprotect
ActiveCell.Offset(-1, 0).Locked = True
ActiveSheet.Protect
Else: End If
End Sub


    This will mean that if you enter something in B7 and press enter (and you have enter to drop down to next cell in column (default setting in Excel)) then cell B& will then have the attribute 'locked'.


    If you're not protecting the workbook then it's likely a lot more complicated (certainly i'm stumped).


    HTH


    KJ

  • Checking If A Value Is Present In A Cell If So Changing To The Cell Below

    Re: Checking If A Value Is Present In A Cell If So Changing To The Cell Below


    Sorry, forgot to add the original offset. :rolleyes:


    Code
    Private Sub CommandButton1_Click()
    Range("B65536").End(xlUp).Offset(1, 0).Select
    With ActiveCell
        .Value = TextBox1.Value
        .Offset(0, 1) = TextBox2.Value
        .Offset(0, 2) = TextBox3.Value
         'Add more Offsets here for each field in your input form.
    End With
End Sub


    That should work now.


    KJ

  • Checking If A Value Is Present In A Cell If So Changing To The Cell Below

    Re: Checking If A Value Is Present In A Cell If So Changing To The Cell Below


    Quote

    Is working great! So if i wish to add more, i purely add more Offsets for the next textboxes


    Yes.


    Quote

    I don't see the point in looping through the cells when .End(xlUp) finds the cell without. That's how I create all my Data Entry Forms


    You're quite right royUK, my bad habits creeping in.


    Code
    Private Sub CommandButton1_Click() 
Range("B65536").End(xlUp).Select
    With ActiveCell 
        .Value = TextBox1.Value 
        .Offset(0, 1) = TextBox2.Value 
        .Offset(0, 2) = TextBox3.Value 
         'Add more Offsets here for each field in your input form.
    End With 
End Sub


    HTH


    KJ

  • Checking If A Value Is Present In A Cell If So Changing To The Cell Below

    Re: Checking If A Value Is Present In A Cell If So Changing To The Cell Below


    Okay, first off don't use TextBox1_change use


    Code
    Private Sub TextBox1_AfterUpdate()
Dim targetrange As String
targetrange = Range("B3").Value
Do Until targetrange = ""
    ActiveCell.Offset(1, 0).Select
    targetrange = ActiveCell.Value
Loop
ActiveCell.Value = TextBox1.Value
End Sub


    This will take you to the next available row in that column, and place the respective value in the cell.


    There is probably a cleaner way of doing it by using ActiveCell.Offset and also using the 'Add to Database' button rather than using TextBox_afterupdate for each box.


    e.g.



    HTH


    KJ