How to click macro - Move right 15 cells - Next click move back to home position

I've been asked to make a macro to do the following.

1st . . . The screen has a freeze pane set at J12 . The activecell can be anyplace on the worksheet. At the beginning of the macro, I will either move the activecell to column I on the same row, or select the entire row.

2nd . . . As an example, the operator makes an entry of 5 into cell I 9. This number represents the number of columns, to the right of column I, that need to be moved to the left.

3rd . . . When the macro is activated, column O would then be located next to column I . I do not want this to be done by hiding columns J through N.

4th . . . I've tried to write a macro that would do this move but no luck yet. I can move to any particular cell, but naturally, I don't know how to change the code according to the number in I 9 .

Then,

The 2nd time the macro is activated, I want all the columns to be re-displayed.

The next time the macro is activated, the first procedure would be run again. If the operator had changed the # in I 9 to 23 , the column AG would be displayed immediately to the right of column I .

So to speak, it's to act like a toggle switch.

Well, I hope I've made my needs clear.

Launchnet;

Check out the ScrollColumn property. If you haven't figured it out by tomorrow when I get back, I'll help you with it.

Hi . . .

The following code is an example of my trying to scroll 3 columns to the right and nothing happens. I also tried same with left and nothing happens.

J12 is the 1st column to the right of the freeze line. I want J K & L to move to the left so-to-speak, where they can not be seen.

Can anyone give me any ideas ?

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Hi shg . . .

Previously I tried the two below ActiveWindow codes (Separately) and they didn't work.
Then I added the Worksheets Activate row of code you suggested (same code) and it
still does not work.

When I tried your suggestion again there was no movement. The macro did run, but nothing happened.

For Reference purposes . . . maybe could be the problem area.
The cell J12 is where the Freeze Pane was set, which I need there.

Please Login or Register  to view this content.

Additional ideas would be appreciated.

Hi Again . . .

I just tested without a Freeze Panes Set and it works.

With either a column Freeze Pane Set or both Horizantal & Vertical set it will not work.

I also tried setting the cursor on the left side of the vertical Freeze Pane and it does not work.

So, I tried the following code and it works part of the way.

Since, I have the addresses hard coded, it goes to the correct column. What I need it to do is look at the number value in cell I 9. If the value in I 9 is 31, I need the active cell at the completion of the macro to be in column 36 or column AJ. I have to write code to calculate what column to return to using the value in I 9. And, I need it to stay on the activerow.

Please Login or Register  to view this content.

There is a second part of this and it is an If statement.

What the If statement will do is work like a toggle switch. 1st click it does what is described above. 2nd click will return to column 10 or J on the same row. Hope this is clear and understandable.
I think I can do it, but I have to run right now. I'll post it tomorrow working or not working.

Hi Launchnet;

I just discovered this thread in my subscriptions. Sorry for not answering earlier.

Don't know if you're still looking for an answer.
The thread isn't marked "Solved", but I don't want to waste your time if you're satisfied.

Hi Foxguy . . .
Thanks for replying. I thought that I had marked it SOLVED, BUT I guess I didn't.

My code works fine, but since my request is so different due to Window Freeze Frames, I would still look at any other code that may also have solved it. I always like to get better with my code, so please send me the link.

Hi Launchnet;

If your code works fine, I wouldn't touch it. As they say "don't fix it if it ain't broke".

If your code isn't working 100% the way you want it, or if you want people to evaluate your code and recommend improvements, then post your code. I'm sure there are people (including me) that would be happy to offer their recommendations.

Thanks foxguy . . . just thought about learning more. OK

Problem, my 1st post does not have a edit button so I can not mark as solved. A couple of postings from the end, 1 post has the edit button and I tried it and it will not go the the Solved screen when I click Advanced. It takes me to a new posting screen

Can you or someone help close this for me?

Originally Posted by Launchnet

Problem, my 1st post does not have a edit button so I can not mark as solved. A couple of postings from the end, 1 post has the edit button and I tried it and it will not go the the Solved screen when I click Advanced. It takes me to a new posting screen

I'm pretty sure you're not looking at the 1st message in the thread. If there really isn't an Edit button on the first message, then something has gone wrong with the web site, so you should contact one of the moderators to help. You can contact them at http://www.excelforum.com/showgroups.php Note, no one else except you can see the Edit button on your messages, so I can't tell if you're missing it or not.

When you "Go Advanced", it is the same screen as a new posting screen. Above the message box, you will see a "Title" box that says "(no prefix)". Select that box and change it to "[SOLVED]" and then click "Save Changes"