Button - rearrange column positions in spreadsheet?possible

[Guest]

Is there possibly a elegant way to allow three different users the
capability to 'rearrange' the positioning of columns in a critical
spreadsheet.

For instance, I have a spreadsheet with 15 columns, but each person wishes
to order these columns differently:

1. Data capturer view - Button
2. DTP operator view - Button
3. Salesman view - Button

Each button would have to rearrange the columns...eg:

Button 1: columns A, G,H,ZY
Button 2: columns: A, D,E,F, K,L,M
Button 3. columns: A, N,M,O,P

Could someone help me with this...greatly appreciated.

Thanks
Jason

 

[Nigel]

Hi Jason
Interesting question!..... AFAIK you could use a cut / paste new column
approach in VBA code eg....

Columns("C:C").Cut
Columns("A:A").Insert Shift:=xlToRight

However you need to keep track of which column is which, since as soon as
you move a column the intervening column references will change as you shift
everything over to make room. Columns to the right of the first cut will
not be affected. So I would recommend that you have a reverse option that
switches everything back to 'normal' before applying a new view.

If the columns never change position - only whether the user sees them or
not. It is probably better to use a hide - unhide approach. This is far
safer than keeping track of columns moving around. In most of your examples
this is true so maybe this might be a better option. (Column ZY in your
post is not valid!)

So you could try and adapt the following.....

Sub dataview()
Columns("B:IV").EntireColumn.Hidden = True
Columns("B").EntireColumn.Hidden = False
Columns("G:G").EntireColumn.Hidden = False
Columns("H:H").EntireColumn.Hidden = False
End Sub

Sub dtpview()
Columns("B:IV").EntireColumn.Hidden = True
Columns("D:F").EntireColumn.Hidden = False
Columns("K:M").EntireColumn.Hidden = False
End Sub

Sub salesview()
Columns("B:IV").EntireColumn.Hidden = True
Columns("M").EntireColumn.Hidden = False
End Sub

 

[Ray Costanzo [MVP]]

Hi Jason,

You can hide columns programatically if that will suffice.

Private Sub cmdButton1_click()
Call SwitchView(1)
End Sub

Private Sub cmdButton2_click()
Call Switchview(2)
End Sub

Private Sub cmdButton3_click()
Call Switchview(3)
End Sub


Sub SwitchView(iView As Integer)
Dim sCols As String
Application.ScreenUpdating = False
ActiveSheet.Columns("A:Z").EntireColumn.Hidden = True
Select Case iView
Case 1: sCols = "A,G:H,Y:Z" '''Data capturer view
Case 2: sCols = "A:A,D:F,K:M" '''DTP operator view
Case 3: sCols = "A:A,N" '''Salesman view
End Select

ActiveSheet.Range(sCols).EntireColumn.Hidden = False
Application.ScreenUpdating = True
ActiveWindow.LargeScroll ToRight:=-1
End Sub


Ray at work

 

[Guest]

Thanks...actually, I already am doing some 'hiding' but I am finding a lot
of resistance or pressue to order the 'unhidden' columns based on user
preference which is unfortunate...

The paste copy solution is interesting although I agree the 'referencing'
could become a problem....hmmmm...

- Jason

 

[Guest]

Thanks Ray you always come up with elegant code...unfortunately
'hide/unhide' only partly solves the problem...I still need a way to allow
the user to arrange these columns in the manner he see fits...

Any other ideas

Thanks
Jason

 

[Ray Costanzo [MVP]]

Well, I'd imagine that you could create a userform that is similar to
something like the toolbar customization dialog in Outlook Express or
something like that, that would allow a person to select columns and
move them up and down. It'd take a decent amount of code to get it
all working.

What about an ASP solution? :]

Perhaps another option is to have a hidden sheet that contains the
actual data, make all the columns named ranges for simplicity, and
then autofill the columns on the displayed sheet with the named
ranges.

Ray at work