userform being pushed off the screen

[Hammer_757]

I have a userform that updates columns of data, one column at a time base on heading selection on the form. After the update occurs the userform is positioned to the right of the column that it provided input to, for review. This can be one of many columns, currently 33 columns.

The problem is that when the userform is positioned to the right of the last column and the excel window is maximized, the userform is off the screen.

Is it possible to make the worksheet scroll enough to the right so that the userform will be visible when the userform is positioned to the right of the last column and the excel window is maximized?


The positioning is done mainly in the last 10 lines

Please Login or Register  to view this content.

I'm attaching the workbook also. the button that activates the code is on row 33 labeled "Update topic"

[Norie]

What input should we make on the userform to illustrate the problem?

PS Have you considered using a listbox for the attendees? It could be set up so each attendee has a checkbox.

[Hammer_757]

Norie,
f you select "orientation" that will show a good alignment between the user form and the cell. then run again and choose "Fall Protection"

I looked at listboxes a little but I did not see where it would be any better. Can you point me to an example of what you are talking about?

thanks for helping!
Robert

[Norie]

Robert

I'll give that a try. In the meantime you might want to look at using Application.Goto to scroll the worksheet to where you want it.

The main advantage of a listbox would be that you would only have 1 control to deal with instead of a separate checkbox for each attendee.

So instead of looping through all controls as you do in the cbt_Enter_Date you would just lop through the listbox.

Here's how you could populate the listbox and set it up to display checkboxes, this would go in the form's Initialize.

Please Login or Register  to view this content.

To loop through it and check which attendess have been selected/checked.

Please Login or Register  to view this content.

[Hammer_757]

Thanks! the listbox with fmListStyleOption set is perfect for my application. Great advice Norie!

Robert

I'll give that a try. In the meantime you might want to look at using Application.Goto to scroll the worksheet to where you want it.

The main advantage of a listbox would be that you would only have 1 control to deal with instead of a separate checkbox for each attendee.

So instead of looping through all controls as you do in the cbt_Enter_Date you would just lop through the listbox.

Here's how you could populate the listbox and set it up to display checkboxes, this would go in the form's Initialize.

Please Login or Register  to view this content.

To loop through it and check which attendess have been selected/checked.

Please Login or Register  to view this content.

[Norie]

I found this Form Positioner .

After downloading the file and importing (dragging) the module modPositioner to your file I tried this.

Please Login or Register  to view this content.

[Hammer_757]

Norie, I was able to use application.goto scroll the worksheet to keep the form on the page. the code follows. I added a bit to prompt the user if they forget to select any attendees which I'm having trouble with. But I will post that in a separate topic.
Thanks again for your help!
Robert

Please Login or Register  to view this content.

[Norie]

Robert

What's the problem with no attendees selected?

As far as I can see the code should handle that.