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

Sub Sort()
    ActiveWorkbook.Worksheets("Updates").AutoFilter.Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Updates").AutoFilter.Sort.SortFields.Add Key:= _
        Range("E2:E1215"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
        :=xlSortNormal
    With ActiveWorkbook.Worksheets("Updates").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

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

Sorry Roy, Not sure your point.

Do you mean the mistake "format code" entry i made 4 replies above , then corrected 3 replies above ?

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

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:

[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 understand

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:

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

Sorry the code I am referreing to / querying in post directly above is :
" If lngNum = 0 Then Exit For "
Thanks Pike

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 :

[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, I have attached the Sample, Its for printing all text up to the 3rd dash. As u see it works perfectly on the first row (Row 6) and the on the 4th Row (Row9) and on the 7th Row (Row 12) So thye pattern of results is : I am looking for the 3rd Dash and every 3rd row gived correct result...

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.

Function FindR1()  
    Dim intLoop  As Integer
    Dim lngNum   As Long
    Dim strInput As String
    Dim strFindWhat As String
    Dim strOutput As String
     
     Dim lngCount As Long
        lngCount = 0
       Do
       If lngCount >= 6 Then Exit Do
    Dim lngWhich As Long
    strFindWhat = "r"
    strInput = ThisWorkbook.Sheets("Sheet1").Cells(6 + lngCount, 2)
    lngWhich = 3
    For intLoop = 1 To lngWhich
        lngNum = InStr(1 + lngNum, strInput, strFindWhat)
   
    Next intLoop
    FindR1 = IIf(lngNum > 0, lngNum, "Not Found")
 ThisWorkbook.Sheets("Sheet1").Cells(6 + lngCount, 3).Value = lngNum


strOutput = Left(strInput, lngNum - 1)
ThisWorkbook.Sheets("Sheet1").Cells(6 + lngCount, 4).Value = strOutput
           lngCount = lngCount + 1
Loop
End Function

Re: Excel Cell characters entries to be limited to 255

Thanks Erstwhile, That really helps - u showed how simple it need be. Great

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

Re: Excel Cell characters entries to be limited to 255

Thanks Erstwin, I will use your code and include Message box telling the "Person inputting the data, that they have to reduce their entry by "x" characters .

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.