Results 1 to 6 of 6
  1. #1
    Lounger
    Join Date
    Jul 2005
    Location
    Bangkok, Bangkok Metropolis, Thailand
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Userform cell referenceing (Windows XP: Office 200

    Dear all
    I am a little experienced in Excel and VB and I am setting up a user interface for my school for teachers completing a protected worksheet. The Excel worksheet has all the generated data protected and the input cells unprotected (until you date the column and save, then it locks). The first 3 cells are protected, the next 7 cells in each row are for grades and marks and the last row (8) is for a <1000 character comment. I want to create a form that has a combo box to select the student,all the other text boxes to view the other cells in that row and to enter/overwrite data into the unprotected cells.

    I have created a working model, but all the linked row cells are linked to the ListIndex and only works if I squash the combo box up to one line, defeats the object, I am really stuck and would welcome any help with this.

    ps
    a nice to have would be that all the cell values change as the user scrolls through the combo box

    Thanks

    Peter_a

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Userform cell referenceing (Windows XP: Office 200

    If you want to leave it open, why not use a listbox instead of a combobox?

    Steve

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Userform cell referenceing (Windows XP: Office 200

    Followup response, you could make the listbox size have height of 13 and add this code tot he form (chage hts as desired)

    <pre>Private Sub ListBox1_Enter()
    ListBox1.Height = 180
    End Sub

    Private Sub ListBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    ListBox1.Height = 13
    End Sub</pre>


    The enter code witll expand it (like the combobox) and the exit will shrink it down again...

    Steve

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Userform cell referenceing (Windows XP: Office 200

    Hello Peter,

    Welcome to Woody's Lounge!

    I wouldn't link the controls on the userform to cells in the worksheet directly, but use VBA code to populate the controls and to save changes back to the worksheet. I have attached a simplistic rexample.

  5. #5
    Lounger
    Join Date
    Jul 2005
    Location
    Bangkok, Bangkok Metropolis, Thailand
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Userform cell referenceing (Windows XP: Office

    Hans
    Thanks for the advise. I didn't know about the 'Me' code and I had forgotten about using the ampersand '&' to add two bits of data. What you sent appears to be exactly what I'm after.

    The protected worksheet is generated by a third party data management software company and first I must check that it will accept the 'Start' command button or whether I must put in a 'Start' macro. Then I must see how I can generate the worksheets and have teachers open the marksheet with the macro/command button prominently available. If you have time, I would appreciate guidance. <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Userform cell referenceing (Windows XP: Office

    You don't have to put a button on the protected worksheet, I just put it in the demo to provide an obvious way to open the userform. There are several alternatives:

    - Create a startup sheet containing a command button that switches to the protected sheet, then opens the form.
    - Put code in the Workbook_Open event in the ThisWorkbook module to open the form.
    - Provide a custom toolbar or custom menu option that runs a macro that opens the form.

Posting Permissions

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