Button - rearrange column positions in spreadsheet?possible
 

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:D, 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

Button - rearrange column positions in spreadsheet?possible
 

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:D").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:P").EntireColumn.Hidden = False
End Sub



--
Cheers
Nigel



wrote in message
...
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:D, 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

Button - rearrange column positions in spreadsheet?possible
 

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:D,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:P" '''Salesman view
End Select

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


Ray at work



wrote in message
...
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:D, 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

Button - rearrange column positions in spreadsheet?possible
 

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

"Nigel" wrote in message
...
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:D").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:P").EntireColumn.Hidden = False
End Sub



--
Cheers
Nigel



wrote in message
...
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:D, 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

Button - rearrange column positions in spreadsheet?possible
 

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]" <my first name at lane 34 dot commercial wrote in
message ...
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:D,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:P" '''Salesman view
End Select

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


Ray at work



wrote in message
...
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:D, 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

Button - rearrange column positions in spreadsheet?possible
 

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



wrote in message
...
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 :)