switch from userform to worksheet

firuz · Mar 21st, 2002, 11:57 AM

I have created userform showing comment text of active cell. I want to scroll to other cells while userform is shown and view their comments in the same userform. But i cant access worksheet cells. What to do?

[ This Message was edited by: firuz on 2002-03-21 12:22 ]

dk · Mar 21st, 2002, 04:19 PM

If you're using Excel 2000 onwards you can show the form as modeless i.e. you can select other objects (e.g. cells) while the form is visible.

Userform1.Show vbModeless

If not then take a look here at a workaround for Excel 97...

http://www.bmsltd.co.uk/DLCount/DLCo...delessForm.zip

HTH,
D

Tom Schreiner · Mar 22nd, 2002, 10:00 AM

You could place four buttons on your form for Left, Right, Up, Down
In each buttons click event place the code using Excels smallscroll method

If you are not familiar with this method, then record a macro and scroll all over the screen to see the different commands.

If you really wanna have fun, draw an image on your form and capture the directional movement of your mouse and use the scrolling methods in the mousemove event.

Have fun and good luck!!!

firuz · Mar 24th, 2002, 02:14 AM

actually i presupposed that i would have to create navigation buttons and thus solve update fix after each selection change. But do you mean that any other way of scrolling on worksheet and simultaneous userform update is impossible?

Dave Hawley · Mar 24th, 2002, 03:43 AM

Hi firuz

You could use a RefEdit control and allow the user to select the cell. But this is really not needed as you can easily fill (say a ComBoBox) with cell addresses of all Comment cells.

Private Sub UserForm_Initialize()
Dim rComments As Range
Dim rCells As Range

On Error Resume Next
Set rComments = Cells.SpecialCells(xlCellTypeComments)
If Not rComments Is Nothing Then
For Each rCells In rComments
ComboBox1.AddItem rCells.Address
Next rCells
End If
On Error GoTo 0

End Sub

firuz · Mar 24th, 2002, 04:49 AM

Dave, i can not preassume what would be my commented cells for pre-entering for combobox.
My main problem now is update of textbox on userform after any event via scrollbar or cmdbutton.on loading userform textbox reflects comment of cell, but then there is no any change.

[ This Message was edited by: firuz on 2002-03-24 03:50 ]

Tom Schreiner · Mar 24th, 2002, 05:22 AM

The workaround given by DK is perfect.
I wish I would have known this when I had 97.
http://www.bmsltd.co.uk/DLCount/DLCo...delessForm.zip

If you need help in editing to suit your purpose, then post the code you are currently using.

And thanks to dk

Dave Hawley · Mar 24th, 2002, 05:46 AM

firuz, I am not sure I understand you. My code doesn't "preassume" the comment cells, it automatically pulls in ONLY the comment cell addresses. If the sheet has none, then no cell addresses are pulled in. If on the other hand Cells A1, D5, H100 had comments these would be the only cell addresses the user would see.

Although Excel 2000 does have modal UserForms, I rarely use the feature as it opens a real can of worms. It's bad enough trying cover all angles on a UserForm without giving the user full access to the Excel interface and all it's features.

The idea behind it is the user would simply choose the cell address from the ComboBox and the comment text in this cell is displayed for them. This must surely be mre user friendly than the user having find then select the comment cell(s).

My second choice would be as I said the RefEdit Control.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 21st, 2002, 11:57 AM	#1
firuz New Member Join Date: Mar 2002 Posts: 7	I have created userform showing comment text of active cell. I want to scroll to other cells while userform is shown and view their comments in the same userform. But i cant access worksheet cells. What to do? [ This Message was edited by: firuz on 2002-03-21 12:22 ]

Mar 21st, 2002, 04:19 PM	#2
dk MrExcel MVP Join Date: Feb 2002 Location: Sydney, Australia Posts: 2,908	If you're using Excel 2000 onwards you can show the form as modeless i.e. you can select other objects (e.g. cells) while the form is visible. Userform1.Show vbModeless If not then take a look here at a workaround for Excel 97... http://www.bmsltd.co.uk/DLCount/DLCo...delessForm.zip HTH, D

Mar 22nd, 2002, 10:00 AM	#3
Tom Schreiner Board Regular Join Date: Mar 2002 Location: Cincinnati, Ohio, USA Posts: 6,824	You could place four buttons on your form for Left, Right, Up, Down In each buttons click event place the code using Excels smallscroll method If you are not familiar with this method, then record a macro and scroll all over the screen to see the different commands. If you really wanna have fun, draw an image on your form and capture the directional movement of your mouse and use the scrolling methods in the mousemove event. Have fun and good luck!!!

Mar 24th, 2002, 02:14 AM	#4
firuz New Member Join Date: Mar 2002 Posts: 7	actually i presupposed that i would have to create navigation buttons and thus solve update fix after each selection change. But do you mean that any other way of scrolling on worksheet and simultaneous userform update is impossible?

Mar 24th, 2002, 03:43 AM	#5
Dave Hawley Banned Join Date: Feb 2002 Posts: 1,582	Hi firuz You could use a RefEdit control and allow the user to select the cell. But this is really not needed as you can easily fill (say a ComBoBox) with cell addresses of all Comment cells. Private Sub UserForm_Initialize() Dim rComments As Range Dim rCells As Range On Error Resume Next Set rComments = Cells.SpecialCells(xlCellTypeComments) If Not rComments Is Nothing Then For Each rCells In rComments ComboBox1.AddItem rCells.Address Next rCells End If On Error GoTo 0 End Sub

Mar 24th, 2002, 04:49 AM	#6
firuz New Member Join Date: Mar 2002 Posts: 7	Dave, i can not preassume what would be my commented cells for pre-entering for combobox. My main problem now is update of textbox on userform after any event via scrollbar or cmdbutton.on loading userform textbox reflects comment of cell, but then there is no any change. [ This Message was edited by: firuz on 2002-03-24 03:50 ]

Mar 24th, 2002, 05:22 AM	#7
Tom Schreiner Board Regular Join Date: Mar 2002 Location: Cincinnati, Ohio, USA Posts: 6,824	The workaround given by DK is perfect. I wish I would have known this when I had 97. http://www.bmsltd.co.uk/DLCount/DLCo...delessForm.zip If you need help in editing to suit your purpose, then post the code you are currently using. And thanks to dk

Mar 24th, 2002, 05:46 AM	#8
Dave Hawley Banned Join Date: Feb 2002 Posts: 1,582	firuz, I am not sure I understand you. My code doesn't "preassume" the comment cells, it automatically pulls in ONLY the comment cell addresses. If the sheet has none, then no cell addresses are pulled in. If on the other hand Cells A1, D5, H100 had comments these would be the only cell addresses the user would see. Although Excel 2000 does have modal UserForms, I rarely use the feature as it opens a real can of worms. It's bad enough trying cover all angles on a UserForm without giving the user full access to the Excel interface and all it's features. The idea behind it is the user would simply choose the cell address from the ComboBox and the comment text in this cell is displayed for them. This must surely be mre user friendly than the user having find then select the comment cell(s). My second choice would be as I said the RefEdit Control.