align columns before opening userform (1 Viewer)

[ramblon]

Hi,
I have a UserForm that opens when I double click in a cell in column H. I'd like to position column H on the left of the screen before opening the UserForm that's positioned on the right of the screen - so that the double click to open the UserForm isn't also taken in error as a click on the UserForm. How do I position column H on the left.
Thanks for any help.

 

[Micron]

Excel has userforms, cells and columns; Access does not. It has forms, fields and records (not that records equates to columns, because it doesn't). I realize you've posted in an Access subforum, but we do have Excel threads here too. So is this Access or not? It will make a difference. If it's Access, then the correct terminology would be helpful, otherwise we may mistake "cell" for a field in a table, when you really mean a control on a form.

 

[ramblon]

Thanks Micron,
Yes you're right, I posted in the wrong place - it's late and hot - 38C - here, . This is an Excel problem not Access.
Do you have any ideas how I can do this? Or should I move this question to another forum?
Thanks for your help

 

[deletedT]

ActiveSheet.Range("H:H").HorizontalAlignment = xlLeft

 

[Micron]

I've forgotten a lot of my Excel objects stuff but I think that suggestion is for aligning the contents of the cell, as in justified, align right, align left, etc. I think what you're asking is to move H:H to where A:A is? If you can, I don't know how. What you could try is turning on the macro recorder and doing it manually, then look at the generated code to see how it's done. Then you could incorporate that into your current procedure - assuming no one here chimes in with the code to move a column. Watch out for any formulae that have exact (i.e. not relative) references such as H$ otherwise you'll break the formula.

Does it matter if the form takes a click? Did you consider controlling where the form opens instead? I know Access has that capability using the MoveSize event.
P.S. can you send some of that heat my way? High of about 13C today.

 

[ramblon]

Thanks Micron,
Yes I want to horizontally scroll the ActiveSheet so that column H is on the left, I've already positioned the UserForm on the right, I'll try the macro recorder as you suggest.
Like to oblige with the weather, but don't have the skill set.

 

[Micron]

I still misunderstood but I think I get it now. You don't want to move the column, you want to scroll the sheet so that H:H is on the left. The recorder will probably give you code for an n count in small steps, which will move n steps no matter where you are. Thus if you're at H already it will move n more, which I suppose is not what you want. There is ActiveWindow.ScrollColumn = n (where n is a number you specify - probably 7) which might work as long as you have an active window at the time. I don't know if your form will cause no window to be active when the form command button has the focus because I have not used many Excel userforms and not for quite some time now.

 

[deletedT]

As @Micron explained, the following line takes H column to the left of screen, no matter which cell is activated.
ActiveWindow.ScrollColumn = 8

 

[Isaac]

Or you could just programmatically adjust the position of userform on the screen

 

[Micron]

Or you could just programmatically adjust the position of userform on the screen

That was suggested already in post 5. I'll take that as seconding the motion

 

[Isaac]

Yeah I would recommend doing both, combining the screen freezing suggestions and the userform positioning