data validation input messages character limit

  • Thread starter Thread starter Peter Bailey
  • Start date Start date
P

Peter Bailey

I have been using this facility in excel to give the user an indication of
the codes to enter, however, it is too restrictive and doesnt allow enough
text to be input.

How would I get a dialog box to pop up dependant upon which column they were
in ie if they move to another column the dialog box text changes to give the
new input messages. it would need to be visible and allow the user to input
data into the cell.

I am not sure what event to usebut I am competent enough in VB.

Regards in advance
peter
 
Peter

I would suspect you would be able to do something with the
Worksheet_SelectionChange() event. To save it appearing each time the user
moved, even if in the same column you may want to save that somewhere. (I've
used a workbook name but you could use a hidden sheet for example)

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Application.Intersect(Target, Columns("A:D")) Is Nothing Then
If CInt(Target.Column) = Right(ThisWorkbook.Names("ColVal").Value, 1)
Then Exit Sub
ThisWorkbook.Names("ColVal").Value = Target.Column
Select Case Target.Column
Case Is = 1
MsgBox "blah1"
Case Is = 2
MsgBox "blah2"
Case Is = 3
MsgBox "blah3"
Case Is = 4
MsgBox "blah4"
End Select
End If
ThisWorkbook.Names("ColVal").Value = Target.Column
End Sub



--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
 
You could use comments to display the input message. If you don't have
other comments on the sheet, you could use event code similar to the
following. In this example, the comment text is entered in named cells
on sheet1, e.g. the cell that contains the comment for column 3 is named
Comment3.

'===============================
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim cmt As Comment
Dim ws As Worksheet
Dim i As Integer
Dim lArea As Double
Set ws = Sheets("Sheet1")

For Each cmt In ActiveSheet.Comments
cmt.Delete
Next

If Target.Count > 1 Then Exit Sub
i = Target.Column

Select Case i
Case 3
Target.AddComment _
Text:=ws.Range("Comment" & i).Text
Target.Comment.Visible = True
Case 4
Target.AddComment _
Text:=ws.Range("Comment" & i).Text
Target.Comment.Visible = True
Case Else
'do nothing
End Select

If Not Target.Comment Is Nothing Then
With Target.Comment
.Shape.TextFrame.AutoSize = True
If .Shape.Width > 300 Then
lArea = .Shape.Width * .Shape.Height
.Shape.Width = 200
.Shape.Height = (lArea / 200) * 1.2
End If
End With

End If

End Sub
'=====================
 
Back
Top