switch from userform to worksheet

firuz

New Member
Joined
Mar 18, 2002
Messages
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
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
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!!!
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,150
Members
448,552
Latest member
WORKINGWITHNOLEADER

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top