Display input message in textbox - with merged cells

Hi there,

I am using the code from

http://www.contextures.com/excelfiles.html

to display input messages in a textbox. It is working fine unless a cell
happens to be merged.

Does anyone know if it is possible to get it to work with merged cells as
well?

Help much appreciated.
--
Regards,

Martin

hard to tell without seeing the code, but a textbox on a userform has no
problem with merged cells

"Martin" wrote:

> Hi there,
>
> I am using the code from
>
> http://www.contextures.com/excelfiles.html
>
> to display input messages in a textbox. It is working fine unless a cell
> happens to be merged.
>
> Does anyone know if it is possible to get it to work with merged cells as
> well?
>
> Help much appreciated.
> --
> Regards,
>
> Martin

Appear to be over 80 files on that page.

--
Regards,
Tom Ogilvy


"Martin" <[email protected]> wrote in message
news:[email protected]...
> Hi there,
>
> I am using the code from
>
> http://www.contextures.com/excelfiles.html
>
> to display input messages in a textbox. It is working fine unless a cell
> happens to be merged.
>
> Does anyone know if it is possible to get it to work with merged cells as
> well?
>
> Help much appreciated.
> --
> Regards,
>
> Martin

Please follow the link below where everything is explained plus the code.

http://www.contextures.com/excelfiles.html

then goto the heading

"Input Message in Textbox"
--
Regards,

Martin


"Patrick Molloy" wrote:

> hard to tell without seeing the code, but a textbox on a userform has no
> problem with merged cells
>
> "Martin" wrote:
>
> > Hi there,
> >
> > I am using the code from
> >
> > http://www.contextures.com/excelfiles.html
> >
> > to display input messages in a textbox. It is working fine unless a cell
> > happens to be merged.
> >
> > Does anyone know if it is possible to get it to work with merged cells as
> > well?
> >
> > Help much appreciated.
> > --
> > Regards,
> >
> > Martin

this modification worked for me.

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim strTitle As String
Dim strMsg As String
Dim sTemp As Shape
Dim ws As Worksheet
Application.EnableEvents = False
Set ws = ActiveSheet
Set sTemp = ws.Shapes("txtInputMsg")
On Error GoTo errHandler
If Intersect(Target(1), ws.Cells.SpecialCells(xlCellTypeAllValidation)) Is
Nothing Then
sTemp.TextFrame.Characters.Text = ""
sTemp.Visible = msoFalse
Else
If Target(1).Validation.InputTitle <> "" Or _
Target(1).Validation.InputMessage <> "" Then
strTitle = Target.Validation.InputTitle & Chr(10)
strMsg = Target(1).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

--
Regards,
Tom Ogilvy

"Martin" <[email protected]> wrote in message
news:[email protected]...
> Please follow the link below where everything is explained plus the code.
>
> http://www.contextures.com/excelfiles.html
>
> then goto the heading
>
> "Input Message in Textbox"
> --
> Regards,
>
> Martin
>
>
> "Patrick Molloy" wrote:
>
> > hard to tell without seeing the code, but a textbox on a userform has no
> > problem with merged cells
> >
> > "Martin" wrote:
> >
> > > Hi there,
> > >
> > > I am using the code from
> > >
> > > http://www.contextures.com/excelfiles.html
> > >
> > > to display input messages in a textbox. It is working fine unless a
cell
> > > happens to be merged.
> > >
> > > Does anyone know if it is possible to get it to work with merged cells
as
> > > well?
> > >
> > > Help much appreciated.
> > > --
> > > Regards,
> > >
> > > Martin

I see. When I merge two cells the validation seems to be accross both. With
the merged cells selected, click Data/Validation an dyou'll get a warning:
"The Selection contains some cells without data Validation Settings. ...."
Click YES to extend the rule.
You will find that the text boxes will work on these cells too now.

"Martin" wrote:

> Please follow the link below where everything is explained plus the code.
>
> http://www.contextures.com/excelfiles.html
>
> then goto the heading
>
> "Input Message in Textbox"
> --
> Regards,
>
> Martin
>
>
> "Patrick Molloy" wrote:
>
> > hard to tell without seeing the code, but a textbox on a userform has no
> > problem with merged cells
> >
> > "Martin" wrote:
> >
> > > Hi there,
> > >
> > > I am using the code from
> > >
> > > http://www.contextures.com/excelfiles.html
> > >
> > > to display input messages in a textbox. It is working fine unless a cell
> > > happens to be merged.
> > >
> > > Does anyone know if it is possible to get it to work with merged cells as
> > > well?
> > >
> > > Help much appreciated.
> > > --
> > > Regards,
> > >
> > > Martin

That's excellent - thank you very, very much
--
Regards,

Martin


"Tom Ogilvy" wrote:

> this modification worked for me.
>
> Option Explicit
>
> Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> Dim strTitle As String
> Dim strMsg As String
> Dim sTemp As Shape
> Dim ws As Worksheet
> Application.EnableEvents = False
> Set ws = ActiveSheet
> Set sTemp = ws.Shapes("txtInputMsg")
> On Error GoTo errHandler
> If Intersect(Target(1), ws.Cells.SpecialCells(xlCellTypeAllValidation)) Is
> Nothing Then
> sTemp.TextFrame.Characters.Text = ""
> sTemp.Visible = msoFalse
> Else
> If Target(1).Validation.InputTitle <> "" Or _
> Target(1).Validation.InputMessage <> "" Then
> strTitle = Target.Validation.InputTitle & Chr(10)
> strMsg = Target(1).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
>
> --
> Regards,
> Tom Ogilvy
>
> "Martin" <[email protected]> wrote in message
> news:[email protected]...
> > Please follow the link below where everything is explained plus the code.
> >
> > http://www.contextures.com/excelfiles.html
> >
> > then goto the heading
> >
> > "Input Message in Textbox"
> > --
> > Regards,
> >
> > Martin
> >
> >
> > "Patrick Molloy" wrote:
> >
> > > hard to tell without seeing the code, but a textbox on a userform has no
> > > problem with merged cells
> > >
> > > "Martin" wrote:
> > >
> > > > Hi there,
> > > >
> > > > I am using the code from
> > > >
> > > > http://www.contextures.com/excelfiles.html
> > > >
> > > > to display input messages in a textbox. It is working fine unless a
> cell
> > > > happens to be merged.
> > > >
> > > > Does anyone know if it is possible to get it to work with merged cells
> as
> > > > well?
> > > >
> > > > Help much appreciated.
> > > > --
> > > > Regards,
> > > >
> > > > Martin
>
>
>