Power Query Course in Spanish
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: switch from userform to worksheet

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sydney, Australia
    Posts
    2,940
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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!!!

  4. #4
    New Member
    Join Date
    Mar 2002
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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?

  5. #5
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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


  6. #6
    New Member
    Join Date
    Mar 2002
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  7. #7
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  8. #8
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.





Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •