Userform cell referenceing (Windows XP: Office 200

[peter_a]

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

[sdckapr]

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

Steve

[sdckapr]

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

[HansV]

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.

[peter_a]

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>

[HansV]

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.