Send cell value to custom pane based on current cell/row?

[rman666]

I'm using Excel 2003 and I have a sheet, about 200 rows with 6 columns. The
first 5 columns are rather simple list selections (i.e., hi, med, low; open,
closed; etc.). But, the last column can be a large amount of text.

I'd like to design a sheet or form that can let me scroll up and down thru
the sheet, and depending on the row I select, it will show the text from the
6th column cell in that row in a scrollable box on the side. It can be either
a custom control or a custom panel (like the help or search panels). The
sheet needs to be sortable by any of the first 5 columns.

Is this possible? I'm willing to do whatever VBA or other coding, but I need
some pointers.

Thanks in advance!

clint@robotic.com

[Earl Kiosterud]

Clint,

For a cheap approach, a macro could set the WrapText property of the text
cell to TRUE for the selected row. This will cause the row height to
increase until all the text is displayed. It will reset the Wrap Text
property of the last row, and then set that of the currently selected row.
This depends on Autofit being set in all your rows. If you've manually
adjusted row heights, autofit will be off, and it won't work without some
additional code.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Static RowOld As Long
Dim RowCurrent As Long
Const ColText = 6
If RowOld > 0 Then ' not first time?
RowCurrent = ActiveCell.Row
Cells(RowOld, ColText).WrapText = False ' reset old row
RowOld = RowCurrent ' save old row number
Cells(RowCurrent, ColText).WrapText = True ' set current row
Else ' first time
RowOld = ActiveCell.Row
End If
End Sub

This goes in the sheet module. Change constant Coltext to that of the
column containing the text.

Another approach would be the use of a modeless userform (XL 2000 and up)
which would stay on the screen. A macro could put the current text into
this form as cells are selected.
--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

"rman666" <rman666@discussions.microsoft.com> wrote in message
news:65A35D15-3D91-4323-BB74-E83FC6BEB8E1@microsoft.com...
> I'm using Excel 2003 and I have a sheet, about 200 rows with 6 columns.
> The
> first 5 columns are rather simple list selections (i.e., hi, med, low;
> open,
> closed; etc.). But, the last column can be a large amount of text.
>
> I'd like to design a sheet or form that can let me scroll up and down thru
> the sheet, and depending on the row I select, it will show the text from
> the
> 6th column cell in that row in a scrollable box on the side. It can be
> either
> a custom control or a custom panel (like the help or search panels). The
> sheet needs to be sortable by any of the first 5 columns.
>
> Is this possible? I'm willing to do whatever VBA or other coding, but I
> need
> some pointers.
>
> Thanks in advance!
>
> clint@robotic.com

[rman666]

Sorry, I wasn't able to get your code to work. I made sure the row height for
all cells was set to AutoFit (selected all cells then selected Format | Row |
AutoFit). Then I went into the Visual Basic Editor (Alt+F11), selected Sheet1
in the Project Explorer and pasted in your code exactly as it was (is below).
When I select a row, nothing change.

Even if we can get this to work, do you have any thoughts on the
sidebar/window approach?

Thanks for your help.

-- Clint (clint@robotic.com)


"Earl Kiosterud" wrote:

> Clint,
>
> For a cheap approach, a macro could set the WrapText property of the text
> cell to TRUE for the selected row. This will cause the row height to
> increase until all the text is displayed. It will reset the Wrap Text
> property of the last row, and then set that of the currently selected row.
> This depends on Autofit being set in all your rows. If you've manually
> adjusted row heights, autofit will be off, and it won't work without some
> additional code.
>
> Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> Static RowOld As Long
> Dim RowCurrent As Long
> Const ColText = 6
> If RowOld > 0 Then ' not first time?
> RowCurrent = ActiveCell.Row
> Cells(RowOld, ColText).WrapText = False ' reset old row
> RowOld = RowCurrent ' save old row number
> Cells(RowCurrent, ColText).WrapText = True ' set current row
> Else ' first time
> RowOld = ActiveCell.Row
> End If
> End Sub
>
> This goes in the sheet module. Change constant Coltext to that of the
> column containing the text.
>
> Another approach would be the use of a modeless userform (XL 2000 and up)
> which would stay on the screen. A macro could put the current text into
> this form as cells are selected.
> --
> Earl Kiosterud
> mvpearl omitthisword at verizon period net
> -------------------------------------------
>
> "rman666" <rman666@discussions.microsoft.com> wrote in message
> news:65A35D15-3D91-4323-BB74-E83FC6BEB8E1@microsoft.com...
> > I'm using Excel 2003 and I have a sheet, about 200 rows with 6 columns.
> > The
> > first 5 columns are rather simple list selections (i.e., hi, med, low;
> > open,
> > closed; etc.). But, the last column can be a large amount of text.
> >
> > I'd like to design a sheet or form that can let me scroll up and down thru
> > the sheet, and depending on the row I select, it will show the text from
> > the
> > 6th column cell in that row in a scrollable box on the side. It can be
> > either
> > a custom control or a custom panel (like the help or search panels). The
> > sheet needs to be sortable by any of the first 5 columns.
> >
> > Is this possible? I'm willing to do whatever VBA or other coding, but I
> > need
> > some pointers.
> >
> > Thanks in advance!
> >
> > clint@robotic.com
>
>
>

[Earl Kiosterud]

Clint,

Make sure you've change ColText to that of the column containing the text to
enlarge. I've set it to 6 for column F. If it still doesn't work, press F9
with the cursor in the If RowOld > 0 ... line. Then Alt-Tab back to Excel
and select a different row. It should stop on the line you've F9'd. Press
F5 to continue, or F8 to step it and see what it's doing.

--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

"rman666" <rman666@discussions.microsoft.com> wrote in message
news:682F7F5B-292C-46CA-9905-E09852B3A2A5@microsoft.com...
> Sorry, I wasn't able to get your code to work. I made sure the row height
> for
> all cells was set to AutoFit (selected all cells then selected Format |
> Row |
> AutoFit). Then I went into the Visual Basic Editor (Alt+F11), selected
> Sheet1
> in the Project Explorer and pasted in your code exactly as it was (is
> below).
> When I select a row, nothing change.
>
> Even if we can get this to work, do you have any thoughts on the
> sidebar/window approach?
>
> Thanks for your help.
>
> -- Clint (clint@robotic.com)
>
>
> "Earl Kiosterud" wrote:
>
>> Clint,
>>
>> For a cheap approach, a macro could set the WrapText property of the text
>> cell to TRUE for the selected row. This will cause the row height to
>> increase until all the text is displayed. It will reset the Wrap Text
>> property of the last row, and then set that of the currently selected
>> row.
>> This depends on Autofit being set in all your rows. If you've manually
>> adjusted row heights, autofit will be off, and it won't work without some
>> additional code.
>>
>> Private Sub Worksheet_SelectionChange(ByVal Target As Range)
>> Static RowOld As Long
>> Dim RowCurrent As Long
>> Const ColText = 6
>> If RowOld > 0 Then ' not first time?
>> RowCurrent = ActiveCell.Row
>> Cells(RowOld, ColText).WrapText = False ' reset old row
>> RowOld = RowCurrent ' save old row number
>> Cells(RowCurrent, ColText).WrapText = True ' set current row
>> Else ' first time
>> RowOld = ActiveCell.Row
>> End If
>> End Sub
>>
>> This goes in the sheet module. Change constant Coltext to that of the
>> column containing the text.
>>
>> Another approach would be the use of a modeless userform (XL 2000 and up)
>> which would stay on the screen. A macro could put the current text into
>> this form as cells are selected.
>> --
>> Earl Kiosterud
>> mvpearl omitthisword at verizon period net
>> -------------------------------------------
>>
>> "rman666" <rman666@discussions.microsoft.com> wrote in message
>> news:65A35D15-3D91-4323-BB74-E83FC6BEB8E1@microsoft.com...
>> > I'm using Excel 2003 and I have a sheet, about 200 rows with 6 columns.
>> > The
>> > first 5 columns are rather simple list selections (i.e., hi, med, low;
>> > open,
>> > closed; etc.). But, the last column can be a large amount of text.
>> >
>> > I'd like to design a sheet or form that can let me scroll up and down
>> > thru
>> > the sheet, and depending on the row I select, it will show the text
>> > from
>> > the
>> > 6th column cell in that row in a scrollable box on the side. It can be
>> > either
>> > a custom control or a custom panel (like the help or search panels).
>> > The
>> > sheet needs to be sortable by any of the first 5 columns.
>> >
>> > Is this possible? I'm willing to do whatever VBA or other coding, but I
>> > need
>> > some pointers.
>> >
>> > Thanks in advance!
>> >
>> > clint@robotic.com
>>
>>
>>

[rman666]

Not sure why. But it is working for me now. I must have missed a character
when I cut and paste earlier. Thanks.

However, I'm more convinced then ever that this solution won't work for me.

I really need to have two side-by-side windows with my spreadsheet on the
left side and a scroll-able text window on the right side. The spreadsheet
will have 5 columns. The 6th column should be hidden with the contents of its
cells displayed (and editable) in the scrolling text window on the right. The
6th column cell displayed on the right should be based on which row is
selected in the spreadsheet.

So, to start, is it possible to to create two side-by-side windows with a
spreadsheet on the left and a scrolling editable multiline text box on the
right?

Thanks for your help!

-- Clint (clint@robotic.com)


"Earl Kiosterud" wrote:

> Clint,
>
> Make sure you've change ColText to that of the column containing the text to
> enlarge. I've set it to 6 for column F. If it still doesn't work, press F9
> with the cursor in the If RowOld > 0 ... line. Then Alt-Tab back to Excel
> and select a different row. It should stop on the line you've F9'd. Press
> F5 to continue, or F8 to step it and see what it's doing.
>
> --
> Earl Kiosterud
> mvpearl omitthisword at verizon period net
> -------------------------------------------
>
> "rman666" <rman666@discussions.microsoft.com> wrote in message
> news:682F7F5B-292C-46CA-9905-E09852B3A2A5@microsoft.com...
> > Sorry, I wasn't able to get your code to work. I made sure the row height
> > for
> > all cells was set to AutoFit (selected all cells then selected Format |
> > Row |
> > AutoFit). Then I went into the Visual Basic Editor (Alt+F11), selected
> > Sheet1
> > in the Project Explorer and pasted in your code exactly as it was (is
> > below).
> > When I select a row, nothing change.
> >
> > Even if we can get this to work, do you have any thoughts on the
> > sidebar/window approach?
> >
> > Thanks for your help.
> >
> > -- Clint (clint@robotic.com)
> >
> >
> > "Earl Kiosterud" wrote:
> >
> >> Clint,
> >>
> >> For a cheap approach, a macro could set the WrapText property of the text
> >> cell to TRUE for the selected row. This will cause the row height to
> >> increase until all the text is displayed. It will reset the Wrap Text
> >> property of the last row, and then set that of the currently selected
> >> row.
> >> This depends on Autofit being set in all your rows. If you've manually
> >> adjusted row heights, autofit will be off, and it won't work without some
> >> additional code.
> >>
> >> Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> >> Static RowOld As Long
> >> Dim RowCurrent As Long
> >> Const ColText = 6
> >> If RowOld > 0 Then ' not first time?
> >> RowCurrent = ActiveCell.Row
> >> Cells(RowOld, ColText).WrapText = False ' reset old row
> >> RowOld = RowCurrent ' save old row number
> >> Cells(RowCurrent, ColText).WrapText = True ' set current row
> >> Else ' first time
> >> RowOld = ActiveCell.Row
> >> End If
> >> End Sub
> >>
> >> This goes in the sheet module. Change constant Coltext to that of the
> >> column containing the text.
> >>
> >> Another approach would be the use of a modeless userform (XL 2000 and up)
> >> which would stay on the screen. A macro could put the current text into
> >> this form as cells are selected.
> >> --
> >> Earl Kiosterud
> >> mvpearl omitthisword at verizon period net
> >> -------------------------------------------
> >>
> >> "rman666" <rman666@discussions.microsoft.com> wrote in message
> >> news:65A35D15-3D91-4323-BB74-E83FC6BEB8E1@microsoft.com...
> >> > I'm using Excel 2003 and I have a sheet, about 200 rows with 6 columns.
> >> > The
> >> > first 5 columns are rather simple list selections (i.e., hi, med, low;
> >> > open,
> >> > closed; etc.). But, the last column can be a large amount of text.
> >> >
> >> > I'd like to design a sheet or form that can let me scroll up and down
> >> > thru
> >> > the sheet, and depending on the row I select, it will show the text
> >> > from
> >> > the
> >> > 6th column cell in that row in a scrollable box on the side. It can be
> >> > either
> >> > a custom control or a custom panel (like the help or search panels).
> >> > The
> >> > sheet needs to be sortable by any of the first 5 columns.
> >> >
> >> > Is this possible? I'm willing to do whatever VBA or other coding, but I
> >> > need
> >> > some pointers.
> >> >
> >> > Thanks in advance!
> >> >
> >> > clint@robotic.com
> >>
> >>
> >>
>
>
>

[Earl Kiosterud]

Clint,

OK. This will require Excel2000 or up, as it uses a modeless UserForm.
Make UserForm1, put TextBox1 in it, and put in the following code:

In a general module, before any subs or functions:
Public Roww As Long

In a general module, or wherever you want code to run to show the form
(could be in Worksheet_Open):
UserForm1.Show vbModeless

You may want to set the Top and Left properties of the Userform to put it
where you want on the screen

In the Sheet module, this code will display the text cell in TextBox1:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Roww = Target.Row
UserForm1.TextBox1.Text = Cells(Target.Row, 6)
End Sub

In the UserForm1 module (double-click the text box to open it, or View -
Code or press F7). This code will write the contents of TextBox1 into the
cell whenver a change has been made (per keystroke):
Private Sub TextBox1_Change()
ActiveSheet.Cells(Roww, 6) = TextBox1.Value
End Sub

This isn't exactly robust. If you'll be changing to other sheets, we'll
probably need to hide the UserForm and disable some things. And other stuff.
BUt it should get you going.
--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

"rman666" <rman666@discussions.microsoft.com> wrote in message
news:F7D605D5-DDC0-4207-A387-AB9FD010FEE3@microsoft.com...
> Not sure why. But it is working for me now. I must have missed a character
> when I cut and paste earlier. Thanks.
>
> However, I'm more convinced then ever that this solution won't work for
> me.
>
> I really need to have two side-by-side windows with my spreadsheet on the
> left side and a scroll-able text window on the right side. The spreadsheet
> will have 5 columns. The 6th column should be hidden with the contents of
> its
> cells displayed (and editable) in the scrolling text window on the right.
> The
> 6th column cell displayed on the right should be based on which row is
> selected in the spreadsheet.
>
> So, to start, is it possible to to create two side-by-side windows with a
> spreadsheet on the left and a scrolling editable multiline text box on the
> right?
>
> Thanks for your help!
>
> -- Clint (clint@robotic.com)
>
>
> "Earl Kiosterud" wrote:
>
>> Clint,
>>
>> Make sure you've change ColText to that of the column containing the text
>> to
>> enlarge. I've set it to 6 for column F. If it still doesn't work, press
>> F9
>> with the cursor in the If RowOld > 0 ... line. Then Alt-Tab back to
>> Excel
>> and select a different row. It should stop on the line you've F9'd.
>> Press
>> F5 to continue, or F8 to step it and see what it's doing.
>>
>> --
>> Earl Kiosterud
>> mvpearl omitthisword at verizon period net
>> -------------------------------------------
>>
>> "rman666" <rman666@discussions.microsoft.com> wrote in message
>> news:682F7F5B-292C-46CA-9905-E09852B3A2A5@microsoft.com...
>> > Sorry, I wasn't able to get your code to work. I made sure the row
>> > height
>> > for
>> > all cells was set to AutoFit (selected all cells then selected Format |
>> > Row |
>> > AutoFit). Then I went into the Visual Basic Editor (Alt+F11), selected
>> > Sheet1
>> > in the Project Explorer and pasted in your code exactly as it was (is
>> > below).
>> > When I select a row, nothing change.
>> >
>> > Even if we can get this to work, do you have any thoughts on the
>> > sidebar/window approach?
>> >
>> > Thanks for your help.
>> >
>> > -- Clint (clint@robotic.com)
>> >
>> >
>> > "Earl Kiosterud" wrote:
>> >
>> >> Clint,
>> >>
>> >> For a cheap approach, a macro could set the WrapText property of the
>> >> text
>> >> cell to TRUE for the selected row. This will cause the row height to
>> >> increase until all the text is displayed. It will reset the Wrap Text
>> >> property of the last row, and then set that of the currently selected
>> >> row.
>> >> This depends on Autofit being set in all your rows. If you've manually
>> >> adjusted row heights, autofit will be off, and it won't work without
>> >> some
>> >> additional code.
>> >>
>> >> Private Sub Worksheet_SelectionChange(ByVal Target As Range)
>> >> Static RowOld As Long
>> >> Dim RowCurrent As Long
>> >> Const ColText = 6
>> >> If RowOld > 0 Then ' not first time?
>> >> RowCurrent = ActiveCell.Row
>> >> Cells(RowOld, ColText).WrapText = False ' reset old row
>> >> RowOld = RowCurrent ' save old row number
>> >> Cells(RowCurrent, ColText).WrapText = True ' set current row
>> >> Else ' first time
>> >> RowOld = ActiveCell.Row
>> >> End If
>> >> End Sub
>> >>
>> >> This goes in the sheet module. Change constant Coltext to that of the
>> >> column containing the text.
>> >>
>> >> Another approach would be the use of a modeless userform (XL 2000 and
>> >> up)
>> >> which would stay on the screen. A macro could put the current text
>> >> into
>> >> this form as cells are selected.
>> >> --
>> >> Earl Kiosterud
>> >> mvpearl omitthisword at verizon period net
>> >> -------------------------------------------
>> >>
>> >> "rman666" <rman666@discussions.microsoft.com> wrote in message
>> >> news:65A35D15-3D91-4323-BB74-E83FC6BEB8E1@microsoft.com...
>> >> > I'm using Excel 2003 and I have a sheet, about 200 rows with 6
>> >> > columns.
>> >> > The
>> >> > first 5 columns are rather simple list selections (i.e., hi, med,
>> >> > low;
>> >> > open,
>> >> > closed; etc.). But, the last column can be a large amount of text.
>> >> >
>> >> > I'd like to design a sheet or form that can let me scroll up and
>> >> > down
>> >> > thru
>> >> > the sheet, and depending on the row I select, it will show the text
>> >> > from
>> >> > the
>> >> > 6th column cell in that row in a scrollable box on the side. It can
>> >> > be
>> >> > either
>> >> > a custom control or a custom panel (like the help or search panels).
>> >> > The
>> >> > sheet needs to be sortable by any of the first 5 columns.
>> >> >
>> >> > Is this possible? I'm willing to do whatever VBA or other coding,
>> >> > but I
>> >> > need
>> >> > some pointers.
>> >> >
>> >> > Thanks in advance!
>> >> >
>> >> > clint@robotic.com
>> >>
>> >>
>> >>
>>
>>
>>