Posts by Pepe.VBA

    Re: vba sort by date


    I should have stated in the post that the aim is to do a sort by date ascending (oldest to newest) over cell range ("E2:E1215")

    Hi Everyone,
    On my spreadsheet, E2:E1215 has dates , my vba (produced from recorded macro. its not providing the correct result.
    I have searched various posts to do it with vba and all are for very advanced applications of the sort.
    Can someone please assist with the basic vba for this

    Re: Automated change of password


    Thanks SO,
    I just realised / remembered in both Access and Excel the Password I have been enterring (to get into VBA editing) is not accessed via vba code, its via:
    VBA Project Properties / Tabs
    Given the above, automating a change of password (to enter the vba editing window) via VBA can't be done.

    I am trying to have an MS Excel or MS Access file's "VBA editor" password to enter change automatically on a certain date.
    For example my VBA password is A1, commencing the 1st December I want A1 to be a non compliant password and the password to be B1 (programmed via vba code)

    Re: Assign function to Button


    Ok understood, thanks Roy.
    I didn't realise I could edit the post once sent.
    I don't know how the colour Tags got included in 1st post sent either (I tought I jst included code...)
    I will take more care

    Re: Assign function to Button


    Thanks Pike , I forgot the Call Command for this. I still need to correct the error when no filters in place and will go to video link u recommend.

    Re: Assign function to Button


    I try again , code above formatted not correct

    Code
    Sub Clear_Filters1()
        ActiveSheet.ShowAllData  
    End Sub
    '  I want button 19 to initiate Sub called : Clear_Filters1
    '  It needs to incorporate a line that adresses  if there is no filters , no debug runtime error 1004 comes up.."Show all Data Method of worksheet class failed"
    Sub Button19_Click()
    '  Application.WorksheetFunction.Clear_Filters1  ?? No work  
    End Sub

    Hi Everyone,
    I want a button on Excel Sheet to initiate code. I have used buttons to initiate macros (via developer menu), but having trouble having the button initiate code.
    As example : clear Filters Sub below .
    I want button 19 to run the Clear Filters sub AND if there are no current filters in place do not give error. Can u help:

    Code
    [COLOR=#0000ff]Sub[/COLOR] Clear_Filters1() 
        ActiveSheet.ShowAllData 
    [COLOR=#0000ff]End Sub[/COLOR] 
     
     [COLOR=#006400]'  I want button 19 to initiate Sub above called : Clear_Filters1[/COLOR]
     [COLOR=#006400]'  It needs to incorporate a line that adresses  if there is no filters , no debug runtime error 1004 comes up.."Show all Data Method of worksheet class failed"[/COLOR]
    [COLOR=#0000ff]Sub[/COLOR] Button19_Click() 
         [COLOR=#006400]'  Application.WorksheetFunction.Clear_Filters1  ?? No work[/COLOR]
    [COLOR=#0000ff]End Sub[/COLOR]

    Re: Identify position of 2nd "r" in a string


    Thanks Pike, I do Engineering and really like to enhace excel with vba - I find it really interesting.
    A follow on question is :
    I want a button to initiate code. I can easily have buttons ititiate macro , via developer menu, b ut having trouble doing it with code. I have done searches for the answer but cannot quite get there.
    As example : clear Filters .
    I want button 19 to run the Clear Filters sub and if there are no current filters in place do not give error. Can u help:

    Code
    Sub Clear_Filters1()
        ActiveSheet.ShowAllData
    End Sub
    
    
    '  I want button 19 to initiate Sub called : Clear_Filters
    '  It needs to incorporate a line that adresses  if there is no filters , no debug runtime error 1004 comes up.."Show all Data Method of worksheet class failed"
    Sub Button19_Click()
    '  Application.WorksheetFunction.Clear_Filters1  ?? No work
    End Sub

    Re: Identify position of 2nd "r" in a string


    Thanks Pike,


    I just thought the code later stating if lngNum >0 then.... would look after that.


    I think you are stating that by putting the " If lngNum = 0 Then Exit " is making the code more efficient by exiting immediately if no dashes

    Re: Identify position of 2nd "r" in a string


    Hi Pike , Works perfectly thanks. Can you please just tell me the purpose of the added check you made :

    Code
    [COLOR=#0000ff]If[/COLOR] lngNum = 0 [COLOR=#0000ff]Then[/COLOR] Exit [COLOR=#0000ff]For[/COLOR] [COLOR=#006400]'add this check[/COLOR]


    What is the purpose when lower down you have the not found line ? Thanks again Pike

    Re: Identify position of 2nd "r" in a string


    Hi Pike , Can you please assist a little further with this "posions in a string" thread. I am having touble applying teh code in the following application: I am trying to print all text before the 3rd "r" in 6 rows consecutively. The first source cell (for the 6 rows) "the iput" is in B6. I am asigning the coliumn C for the position of thye 3rd "R" and assigning column D for all text before the 3rd "R" . Problems in Column C for position is not alwars correct to what is in Column B (sometimes correct) , It searched to the end of the string and scrolls back to the beginning of the string if the is not 3 "R"'s in the String. Problems in Column D is if there is no number in column C code stops.


    Re: Excel Cell characters entries to be limited to 255


    Hi Erstwhile OzMVP Can I ask you for a little further assistance: I am trying to add a little to code in order to provide a debug.print of specific cells within Range B2:F8) have over (say 5) characters. The intermediate window would print a list of such cells and state how many characters they have. Typically
    B7 Cell characters: 6
    E4 Cell characters: 8

    Hi Friends,
    I am importing a excel worksheet into access , and access only allows 255 characters / imported excel cell. Can some one help with the code for the excel worksheet that would limit the no of characters that can be enterred into range B2:F8 to 255, so after 255 enterred you just cant enter more characters.