255 Character Limit when using Excel 2007 Input Message

Hi,

I am wondering if anyone can help?

I have created a Change Calendar which uses VBA to pull through Change names which appear when you click on the week:

Change Calendar.JPG

The code is working, problem is the 255 character limit in the input message box, is there anyway this limit can be removed or extended?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Static prevRng As Range
    If Not prevRng Is Nothing Then
        prevRng.Validation.Delete
    End If
    If Intersect(Target, Range("C25:O105")) Is Nothing Then Exit Sub
    If Target.Count > 1 Then Exit Sub
    Set prevRng = Target
    With Target.Validation


If ActiveCell.Row < 43 Then GoTo 1 Else
If ActiveCell.Row < 65 Then GoTo 2 Else
If ActiveCell.Row < 87 Then GoTo 3 Else
If ActiveCell.Row < 109 Then GoTo 4 Else
    
    
    '''''''''''''''''''''Q1 Area
1

    If Not prevRng Is Nothing Then
        prevRng.Validation.Delete
    End If
    If Intersect(Target, Range("C25:O39")) Is Nothing Then Exit Sub
    If Target.Count > 1 Then Exit Sub
    Set prevRng = Target
    With Target.Validation
    
    mcol = -ActiveCell.Column + 2
    mcol2 = -ActiveCell.Column + 1
    
   myvar = Cells(24, ActiveCell.Column)
    Range("E7") = myvar
   rvar = ActiveCell.Offset(0, mcol)
     Range("E10").Value = ""
   Range("E8").Value = ""
   Range("E9").Value = ""
   
   
   
   Range("D6") = rvar
   rvar2 = ActiveCell.Offset(0, mcol2)
   Range("C6") = rvar2
   
    
    .Delete
    .Add xlValidateInputOnly
    .InputMessage = Range("F6")
 
    End With
    
  ''''''''''''''''''''''''''''''''''''''Q2 Area
2
    If Not prevRng Is Nothing Then
        prevRng.Validation.Delete
    End If
    If Intersect(Target, Range("C47:O61")) Is Nothing Then Exit Sub
    If Target.Count > 1 Then Exit Sub
    Set prevRng = Target
    With Target.Validation
    
    mcol = -ActiveCell.Column + 2
    mcol2 = -ActiveCell.Column + 1
    
   myvar = Cells(46, ActiveCell.Column)
    Range("E7") = myvar
   rvar = ActiveCell.Offset(0, mcol)
     Range("E10").Value = ""
   Range("E8").Value = ""
   Range("E9").Value = ""
   
   Range("D6") = rvar
   rvar2 = ActiveCell.Offset(0, mcol2)
   Range("C6") = rvar2
   
    
    .Delete
    .Add xlValidateInputOnly
    .InputMessage = Range("F6")
  
    End With
    
    ''''''''''''''''''''''''''''''''''''''Q3 Area
3
    If Not prevRng Is Nothing Then
        prevRng.Validation.Delete
    End If
    If Intersect(Target, Range("C69:O83")) Is Nothing Then Exit Sub
    If Target.Count > 1 Then Exit Sub
    Set prevRng = Target
    With Target.Validation
    
    mcol = -ActiveCell.Column + 2
    mcol2 = -ActiveCell.Column + 1
    
   myvar = Cells(68, ActiveCell.Column)
    Range("E7") = myvar
   rvar = ActiveCell.Offset(0, mcol)
     Range("E10").Value = ""
   Range("E8").Value = ""
   Range("E9").Value = ""
   
   
   
   Range("D6") = rvar
   rvar2 = ActiveCell.Offset(0, mcol2)
   Range("C6") = rvar2
   
    
    .Delete
    .Add xlValidateInputOnly
    .InputMessage = Range("F6")
  
    End With
    
    
   ''''''''''''''''''''''''''''''''''''''Q4 Area
4
    If Not prevRng Is Nothing Then
        prevRng.Validation.Delete
    End If
    If Intersect(Target, Range("C91:O105")) Is Nothing Then Exit Sub
    If Target.Count > 1 Then Exit Sub
    Set prevRng = Target
    With Target.Validation
    
    mcol = -ActiveCell.Column + 2
    mcol2 = -ActiveCell.Column + 1
    
   myvar = Cells(90, ActiveCell.Column)
    Range("E10") = myvar
   rvar = ActiveCell.Offset(0, mcol)
   
   Range("E7").Value = ""
   Range("E8").Value = ""
   Range("E9").Value = ""
   
   
   Range("D6") = rvar
   rvar2 = ActiveCell.Offset(0, mcol2)
   Range("C6") = rvar2
   
    
    .Delete
    .Add xlValidateInputOnly
    .InputMessage = Range("F6")
  
    End With
        End With
End Sub

Hi, Nic31,

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE] [/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here



(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

Ciao,
Holger

HI,

I dont think there is to be honest with you.

YOu could bypass it and do it yourself, so if intersect(target,Target.Worksheet.UsedRange.SpecialCells(xlCellTypeAllValidation)) to tell you if it has validation.
Then handle the rest using a normal message box and logic.

I've written some custom validation using event sinking before now, you can tdo this in Excel 2010 using the worksheet_change event to do so.


Event sinking (older versions) would be something like

In a class, then on workbook open pass each sheet to the class....

Private WithEvents WS As Excel.Worksheet

Public Sub INIT(wsP As Excel.Worksheet)

Set WS = wsP

End Sub

Private Sub WS_SelectionChange(ByVal Target As Range)

Dim r As Range

Set r = Target.Worksheet.UsedRange.SpecialCells(xlCellTypeAllValidation)

If Intersect(r, Target) Is Nothing Then
    MsgBox String(500, "*")
End If

End Sub

Using this event is probably a good place to put it.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

End Sub