show the input message of a data validation list on a textbox and set editable
 

the data validation list only allows 200 characters, i am able to do this but
i cant edit the textbox and add more information to it? any help.

Code works but need to set textbox editable to add more data to current
validation list info

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim strTitle As String
Dim strMsg As String
Dim sTemp As Shape
Dim lDVType As Long
Dim ws As Worksheet
Application.EnableEvents = False
Set ws = ActiveSheet
Set sTemp = ws.Shapes("txtInputMsg")
On Error Resume Next
lDVType = 0
lDVType = Target.Validation.Type
On Error GoTo errHandler
If lDVType = 0 Then
sTemp.TextFrame.Characters.Text = ""
sTemp.Visible = msoFalse
Else
If Target.Validation.InputTitle < "" Or _
Target.Validation.InputMessage < "" Then
strTitle = Target.Validation.InputTitle & Chr(10)
strMsg = Target.Validation.InputMessage
With sTemp.TextFrame
.Characters.Text = strTitle & strMsg
.Characters.Font.Bold = False
.Characters(1, Len(strTitle)).Font.Bold = True
End With
sTemp.Visible = msoTrue
Else
sTemp.TextFrame.Characters.Text = ""
sTemp.Visible = msoFalse
End If
End If
errHandler:
Application.EnableEvents = True

End Sub


created the textbox and named it txtInputMsg

when click on validation list info of input message appears
help to be able to edit the textbox please

show the input message of a data validation list on a textbox and
 

from:
Set ws = ActiveSheet
Set sTemp = ws.Shapes("txtInputMsg")

To:
Set ws = ActiveSheet
Set sTemp = ws.OLEObjects("txtInputMsg").Object
sTemp.Text = "Joel"


"HCS7" wrote:

the data validation list only allows 200 characters, i am able to do this but
i cant edit the textbox and add more information to it? any help.

Code works but need to set textbox editable to add more data to current
validation list info

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim strTitle As String
Dim strMsg As String
Dim sTemp As Shape
Dim lDVType As Long
Dim ws As Worksheet
Application.EnableEvents = False
Set ws = ActiveSheet
Set sTemp = ws.Shapes("txtInputMsg")
On Error Resume Next
lDVType = 0
lDVType = Target.Validation.Type
On Error GoTo errHandler
If lDVType = 0 Then
sTemp.TextFrame.Characters.Text = ""
sTemp.Visible = msoFalse
Else
If Target.Validation.InputTitle < "" Or _
Target.Validation.InputMessage < "" Then
strTitle = Target.Validation.InputTitle & Chr(10)
strMsg = Target.Validation.InputMessage
With sTemp.TextFrame
.Characters.Text = strTitle & strMsg
.Characters.Font.Bold = False
.Characters(1, Len(strTitle)).Font.Bold = True
End With
sTemp.Visible = msoTrue
Else
sTemp.TextFrame.Characters.Text = ""
sTemp.Visible = msoFalse
End If
End If
errHandler:
Application.EnableEvents = True

End Sub


created the textbox and named it txtInputMsg

when click on validation list info of input message appears
help to be able to edit the textbox please

show the input message of a data validation list on a textbox and
 

Joel wrote:
from:
Set ws = ActiveSheet
Set sTemp = ws.Shapes("txtInputMsg")

To:
Set ws = ActiveSheet
Set sTemp = ws.OLEObjects("txtInputMsg").Object
sTemp.Text = "Joel"

the data validation list only allows 200 characters, i am able to do this but
i cant edit the textbox and add more information to it? any help.
[quoted text clipped - 45 lines]
when click on validation list info of input message appears
help to be able to edit the textbox please

Thanks for that, but it still doesn't work, cos now the txtbox doesn't show
anymore

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200811/1

show the input message of a data validation list on a textbox
 

You were getting some errors and the On Error statement was skipping the
code. I commented the ON Error statements until you get the code working. I
made some changes to the code below

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim strTitle As String
Dim strMsg As String
Dim sTemp As Object
Dim lDVType As Long
Dim ws As Worksheet
Application.EnableEvents = False
Set ws = ActiveSheet
Set sTemp = ws.OLEObjects("txtInputMsg").Object

'On Error Resume Next
lDVType = 0
lDVType = Target.Validation.Type
'On Error GoTo errHandler
If lDVType = 0 Then
sTemp.TextFrame.Characters.Text = ""
sTemp.Visible = msoFalse
Else
If Target.Validation.InputTitle < "" Or _
Target.Validation.InputMessage < "" Then
strTitle = Target.Validation.InputTitle & Chr(10)
strMsg = Target.Validation.InputMessage
With sTemp
.Text = strTitle & strMsg
.FontBold = False
'.Characters(1, Len(strTitle)).Font.Bold = True
End With
sTemp.Visible = msoTrue
Else
sTemp.Text = ""
ws.OLEObjects("txtInputMsg").Visible = msoFalse
End If
End If
errHandler:
Application.EnableEvents = True

End Sub


"HCS7 via OfficeKB.com" wrote:

Joel wrote:
from:
Set ws = ActiveSheet
Set sTemp = ws.Shapes("txtInputMsg")

To:
Set ws = ActiveSheet
Set sTemp = ws.OLEObjects("txtInputMsg").Object
sTemp.Text = "Joel"

the data validation list only allows 200 characters, i am able to do this but
i cant edit the textbox and add more information to it? any help.
[quoted text clipped - 45 lines]
when click on validation list info of input message appears
help to be able to edit the textbox please

Thanks for that, but it still doesn't work, cos now the txtbox doesn't show
anymore

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200811/1