vendredi 5 janvier 2018

Why is string search not working?

I am attempting to create an automatically updating worksheet which tracks all the positions of where the text boxes line up in terms of columns. This is to create a gantt chart. basically when i drag the text box it is to tell me what week it is in and every time I change one text box it will update that. The following picture illustrates this well. enter image description here

The issue is when i keep dragging the text box and change positions over and over again, the weeks dont overwrite the previous week7-week13 but just concatenates the new position ontop of the old one as follows: enter image description here I am trying to write an if statement which basically says that if the substring "week" in in the caption then parse the multi-line textbox into lines and overwrite the first line with the new position. This is the code I wrote I just need help with this if statement:

Option Explicit
Public alltxt As String
Private selectText() As String

Private Sub CommandButton1_Click()
    UF1.Show
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim ws As Worksheet
    Set ws = Target.Parent
    Dim temp
    Dim i
    Dim pos

    Dim shp As Shape
    Dim line As Variant
    For Each shp In ws.Shapes   'loop through all shapes
        If shp.Type = msoTextBox Then 'that are text boxes
            'write the header cells into the text box
            temp = shp.OLEFormat.Object.Caption
            **If InStr(temp, "week")= 0 Then**
                shp.OLEFormat.Object.Caption = ws.Cells(1, shp.TopLeftCell.Column).Text & " - " & ws.Cells(1, shp.BottomRightCell.Column).Text & vbNewLine & shp.OLEFormat.Object.Caption
                Else
                    selectText = Split(shp.OLEFormat.Object.Caption, vbNewLine)
                    shp.OLEFormat.Object.Caption = selectText(0)
                    'Next i
                    shp.OLEFormat.Object.Caption = ws.Cells(1, shp.TopLeftCell.Column).Text & " - " & ws.Cells(1, shp.BottomRightCell.Column).Text & vbNewLine & shp.OLEFormat.Object.Caption
                End If

        End If
    Next shp
End Sub

Any push in the right direction would be greatly appreciates. And please keep in mind I am relatively new to vba so this may be a really easy fix.

Thanks in advance

Aucun commentaire:

Enregistrer un commentaire