Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
DATA VALIDATION INPUT MESSAGE | Excel Discussion (Misc queries) | |||
Data Validation - input message | Excel Discussion (Misc queries) | |||
Data Validation Input Message | Excel Discussion (Misc queries) | |||
Data Validation Input Message | Excel Worksheet Functions | |||
DataValidationInput message | Excel Worksheet Functions |