How to move to the next line in InkEdit control

ANWAR ALAM

New Member
Joined
Mar 26, 2021
Messages
18
Office Version
  1. 2010
Platform
  1. Windows
Hi to all, I have a problem while using inkEdit control, taken on Sheet1/ Excel 2010. I have say 6 lines of data in it. In each line, I have written some item names, randomly. I have also taken a ComboBox on Sheet1. When I type say, “W”, in ComboBox, it is searched in inkEdit, according to the following piece of VBA code:
Private Sub ComboBox1_Change()
Dim Start: Start = InStr(Me.InkEdit1.Text, ComboBox1.Text)
InkEdit1.SelStart = Start - 1
InkEdit1.SelLength = Len (ComboBox1.Text)
InkEdit1.SelColor = RGB(255, 0, 0)
End Sub

Suppose the name in the 1st line contains “W”, so after searching, its color is changed to RED. Please see the Image, i have sent in attachment.
But I want that the next five lines should also be searched so that all the “Ws” are changed to RED, if they are present in the next lines too. will be grateful for any possible solution to this. Thanks. Regards!
 

Attachments

  • InkEdit Control.png
    InkEdit Control.png
    8.9 KB · Views: 7

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Dan_W

Well-known Member
Joined
Jul 11, 2018
Messages
637
Office Version
  1. 365
Platform
  1. Windows
The InStr function actually has three arguments, but one of them is optional - the start argument. (MS reference) InStr will only return the location of the first instance of the text it comes across assuming a given starting location. The code you've used implicitly relies on the default starting location, which is obviously the first character of a string. What you will need to do is after finding the location of the text you're looking for, you then need to keep on executing the Instr function with an updating starting location until you've reached the end of the string. This should be whatever the position of the last instance found +1. So in your code above:
VBA Code:
Start = InStr(Me.InkEdit1.Text, ComboBox1.Text)
will need to be adjusted so that you have a new start location with something along the lines of:
VBA Code:
NewInstanceStart = InStr(LastInstanceStart+1, Me.InkEdit1.Text, ComboBox1.Text)
 

ANWAR ALAM

New Member
Joined
Mar 26, 2021
Messages
18
Office Version
  1. 2010
Platform
  1. Windows
The InStr function actually has three arguments, but one of them is optional - the start argument. (MS reference) InStr will only return the location of the first instance of the text it comes across assuming a given starting location. The code you've used implicitly relies on the default starting location, which is obviously the first character of a string. What you will need to do is after finding the location of the text you're looking for, you then need to keep on executing the Instr function with an updating starting location until you've reached the end of the string. This should be whatever the position of the last instance found +1. So in your code above:
VBA Code:
Start = InStr(Me.InkEdit1.Text, ComboBox1.Text)
will need to be adjusted so that you have a new start location with something along the lines of:
VBA Code:
NewInstanceStart = InStr(LastInstanceStart+1, Me.InkEdit1.Text, ComboBox1.Text)
That's great, rather fantastic. Your suggestion is valuable and is worth 1 million dollars. I have no words to say my thanks to you. Your modification is fine and its working but it works only for the first word present in the InkEdit control ie the letter "W" is searched in the first word present in the InkEdit control. please if you guide me further on how to pick up the next word in the InkEdit control. The existing code works finely. I have just applied For...Loop, on it,,,,as u can see plz in attach. What i want is to search the letter "W", in all the words present in the InkEdit control. I have tried List(ListIndex) to move to the next word, but it is not working for InkEdit control. It is working good for comboBox only,,,eg ComboBox1.List(0), ComboBox1.List(1) will pick up/retrieve the first and 2nd word respectively. Once again a ton of thanks to you. Anxiously waiting for your kind answer, please.
 

Attachments

  • Use of InkEdit control.png
    Use of InkEdit control.png
    6.2 KB · Views: 3

Dan_W

Well-known Member
Joined
Jul 11, 2018
Messages
637
Office Version
  1. 365
Platform
  1. Windows
Ok - I can see you what you've done. You're right to use a loop, but you need to think of the InkEdit control similar to the TextBox control - whereas the List and ListIndex properties are relevant to the ComboBox, ListBox, ListView (etc?) controls. You are searching through a single string - Me.InkEdit1.Text - so perhaps the best place to approach it is to look at how to search a Textbox for instances of a substring and try and apply that method to the InkEdit control. Hopefully that makes sense.
 

Dan_W

Well-known Member
Joined
Jul 11, 2018
Messages
637
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

I just looked at your screen capture again, and I think I see what you're doing - you're using the InkEdit control as a pseudo rich text Listbox, right? That's fine, but at the end of the day, you still need to think of the InkEdit control as a textbox.
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,753
Maybe something like this?

VBA Code:
Private Sub ComboBox1_Change()

    With Me.InkEdit1
        .SelStart = 0
        .SelLength = Len(.Text)
        .SelColor = 0
    End With
    
    Dim searchText As String
    searchText = Me.ComboBox1.Value
    
    If Len(searchText) = 0 Then Exit Sub

    Dim pos As Long
    pos = 0
    
    With Me.InkEdit1
        Do
            pos = InStr(pos + 1, .Text, searchText)
            If pos > 0 Then
                .SelStart = pos - 1
                .SelLength = Len(searchText)
                .SelColor = RGB(255, 0, 0)
                pos = pos + Len(searchText) - 1
            End If
        Loop While (pos > 0)
    End With

End Sub
 

ANWAR ALAM

New Member
Joined
Mar 26, 2021
Messages
18
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

Maybe something like this?

VBA Code:
Private Sub ComboBox1_Change()

    With Me.InkEdit1
        .SelStart = 0
        .SelLength = Len(.Text)
        .SelColor = 0
    End With
   
    Dim searchText As String
    searchText = Me.ComboBox1.Value
   
    If Len(searchText) = 0 Then Exit Sub

    Dim pos As Long
    pos = 0
   
    With Me.InkEdit1
        Do
            pos = InStr(pos + 1, .Text, searchText)
            If pos > 0 Then
                .SelStart = pos - 1
                .SelLength = Len(searchText)
                .SelColor = RGB(255, 0, 0)
                pos = pos + Len(searchText) - 1
            End If
        Loop While (pos > 0)
    End With

End Sub
Thanks a lot Domenic for your kind reply. Your code is working but searching of a particular letter(say "W") is done within only the 1st word of InkEdit and r made red coloured. In the next words, "W", is not searched, instead, some other irrelevant letters r selected and made red.Also the letter being searched (eg W) should not be case sensitive. Plz if u revise ur code as i don't know where to make the change. i m sending an uploaded image. Plz check. Thanks again for being lenient to me.
 

Attachments

  • InkEdit Control1.png
    InkEdit Control1.png
    2.7 KB · Views: 0

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,753
Okay, I have amended the macro to take into account line breaks. Does this help?

VBA Code:
Private Sub ComboBox1_Change()

    With Me.InkEdit1
        .SelStart = 0
        .SelLength = Len(.Text)
        .SelColor = 0
    End With
   
    Dim searchText As String
    searchText = Me.ComboBox1.Value
   
    If Len(searchText) = 0 Then Exit Sub

    Dim pos As Long 'character position
    pos = 0
   
    Dim cnt As Long 'line break count
    cnt = 0
   
    With Me.InkEdit1
        Do While pos < Len(.Text)
            If Mid(.Text, pos + 1, 2) = vbCrLf Then
                cnt = cnt + 1
                pos = pos + 2
            ElseIf Mid(.Text, pos + 1, Len(searchText)) = searchText Then
                .SelStart = pos - cnt
                .SelLength = Len(searchText)
                .SelColor = RGB(255, 0, 0)
                pos = pos + Len(searchText)
            Else
                pos = pos + 1
            End If
        Loop
    End With

End Sub
 

Attachments

  • anwar.png
    anwar.png
    3.5 KB · Views: 5
Solution

ANWAR ALAM

New Member
Joined
Mar 26, 2021
Messages
18
Office Version
  1. 2010
Platform
  1. Windows
Okay, I have amended the macro to take into account line breaks. Does this help?

VBA Code:
Private Sub ComboBox1_Change()

    With Me.InkEdit1
        .SelStart = 0
        .SelLength = Len(.Text)
        .SelColor = 0
    End With
  
    Dim searchText As String
    searchText = Me.ComboBox1.Value
  
    If Len(searchText) = 0 Then Exit Sub

    Dim pos As Long 'character position
    pos = 0
  
    Dim cnt As Long 'line break count
    cnt = 0
  
    With Me.InkEdit1
        Do While pos < Len(.Text)
            If Mid(.Text, pos + 1, 2) = vbCrLf Then
                cnt = cnt + 1
                pos = pos + 2
            ElseIf Mid(.Text, pos + 1, Len(searchText)) = searchText Then
                .SelStart = pos - cnt
                .SelLength = Len(searchText)
                .SelColor = RGB(255, 0, 0)
                pos = pos + Len(searchText)
            Else
                pos = pos + 1
            End If
        Loop
    End With

End Sub
Thats great Domenic! Great ideas come from great people. Best wishes and thanks from the abyssmal depths of my heart to you.
 

Forum statistics

Threads
1,143,840
Messages
5,721,100
Members
422,339
Latest member
SHIVATVM

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top