Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 :) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I need to rearrange the information in a column. Help? | Excel Discussion (Misc queries) | |||
I need to rearrange the information in a column. Help? | Excel Discussion (Misc queries) | |||
Help, please - How to rearrange 1 column of data into 4 columns ? | Excel Discussion (Misc queries) | |||
Rearrange numbers from a column in a different order in a second c | Excel Discussion (Misc queries) | |||
How can I sort a column by the last positions of each cell | Excel Discussion (Misc queries) |