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.
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: 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