Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 04-10-2020, 09:32 PM
beno_83au beno_83au is offline Adjust Two Columns Without Changing Combined Size Windows 10 Adjust Two Columns Without Changing Combined Size Office 2019
Novice
Adjust Two Columns Without Changing Combined Size
 
Join Date: Apr 2020
Posts: 5
beno_83au is on a distinguished road
Question Adjust Two Columns Without Changing Combined Size

Hello, I'm finding all the answers I don't need to adjusting column width and I just don't know what else to search for. What I've got is 4 columns sized out to cover a page. The 2 outer columns, A and D, are small and fine where they are, but I don't want them to move or change. Columns B and C have a combined width of 539 pixels. What I'm trying to do is change to size of columns B and C without changing their combined width, in effect using columns A and D as immovable barriers. Is there an easy way to do this without manually changing B and C to equal 539 pixels each time I want to make a change?



Thanks.
Reply With Quote
  #2  
Old 04-11-2020, 08:13 AM
BobBridges's Avatar
BobBridges BobBridges is offline Adjust Two Columns Without Changing Combined Size Windows 7 64bit Adjust Two Columns Without Changing Combined Size Office 2010 32bit
Expert
 
Join Date: May 2013
Location: USA
Posts: 700
BobBridges has a spectacular aura aboutBobBridges has a spectacular aura about
Default

Only way I can think of is to write a VBA program; it'll look at the widths of cols B and C and adjust them appropriately. You can run it periodically and manually, or maybe have it run automatically whenever you make any change to the worksheet—which way is better depends, I suppose, on how much work you do with that worksheet and what kind of changes. I'd say start it as a program you trigger manually, and decide later whether you want to turn t loose to be triggered by changes.

If you don't know how to create such a program, one of us can help, I'm sure :-).

Last edited by BobBridges; 04-11-2020 at 08:15 AM. Reason: Should have added in the first place...
Reply With Quote
  #3  
Old 04-11-2020, 08:27 PM
beno_83au beno_83au is offline Adjust Two Columns Without Changing Combined Size Windows 10 Adjust Two Columns Without Changing Combined Size Office 2019
Novice
Adjust Two Columns Without Changing Combined Size
 
Join Date: Apr 2020
Posts: 5
beno_83au is on a distinguished road
Default

Quote:
Originally Posted by BobBridges View Post
Only way I can think of is to write a VBA program
Cheers Bob. Looks like I might be able to create a macro for this at a quick glance, so we'll see how far I get with that.
Reply With Quote
  #4  
Old 04-12-2020, 03:10 AM
macropod's Avatar
macropod macropod is offline Adjust Two Columns Without Changing Combined Size Windows 7 64bit Adjust Two Columns Without Changing Combined Size Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Try something along the lines of:
Code:
Sub Demo()
Dim tw As Single, cw As Single, nw As Single
With Selection
  If .Columns.Count <> 2 Then Exit Sub
  tw = .Columns.Width
  cw = .Columns(1).Width
  nw = InputBox("The combined column width is: " & tw & vbCr & _
    "The first column width is: " & cw & vbCr & _
    "How wide should the first column be?", , cw)
  With .Columns(1)
    .ColumnWidth = .ColumnWidth * nw / cw
  End With
  With .Columns(2)
    .ColumnWidth = .ColumnWidth * (tw - nw) / (tw - cw)
  End With
End With
End Sub
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #5  
Old 04-13-2020, 06:21 AM
beno_83au beno_83au is offline Adjust Two Columns Without Changing Combined Size Windows 10 Adjust Two Columns Without Changing Combined Size Office 2019
Novice
Adjust Two Columns Without Changing Combined Size
 
Join Date: Apr 2020
Posts: 5
beno_83au is on a distinguished road
Default

Ok, so thanks for the help guys. Having never worked with Excel VBS you got me started in the right direction. Now that I've come up with some code to work with, it's giving me some errors that I have not been able to correct:

Code:
Sub CommandButton1_Click()
Dim columnBWidth As Single, columnCWidth As Single, totalWidth As Single
columnBWidth = Columns("b").ColumnWidth
columnCWidth = Columns("c").ColumnWidth
totalWidth = columnBWidth + columnCWidth
Do While Selection.Columns.Count = 2
With Selection
    Dim columnBNewWidth As Single, columnCAdjustedWidth As Single
    columnBNewWidth = Selection(1).ColumnWidth
    columnCAdjustedWidth = totalWidth - columnBNewWidth
    Worksheets("Sheet1").Range("c:c").ColumnWidth = columnCAdjustedWidth
End With
Loop
MsgBox ("Ended.")
End Sub
So, as stated in the OP, when dragging to resize Column B I'm trying to auto-resize Column C to maintain the original width of B + C. Using some MsgBox'es along the way to test the returns seems good. But when I try to resize C, Excel crashes.

I tried wrapping Worksheets("Sheet1").Range(Columns("c:c")).ColumnW idth = columnCAdjustedWidth with Sub Column_Width() but after looking up that error I learnt that you can't overlap subroutines.

I'm not ashamed to say I've spent all evening on this My experience with writing code is as a self-taught ArmA coder, so I'm used to putting in unreasonable amounts of time to make something work on my own. But now I'm stuck. How do I get this to work?

Last edited by beno_83au; 04-13-2020 at 06:25 AM. Reason: Found an error, changed it, new problem :)
Reply With Quote
  #6  
Old 04-13-2020, 06:29 AM
macropod's Avatar
macropod macropod is offline Adjust Two Columns Without Changing Combined Size Windows 7 64bit Adjust Two Columns Without Changing Combined Size Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Did you try the code I posted?
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #7  
Old 04-13-2020, 06:37 AM
ArviLaanemets ArviLaanemets is offline Adjust Two Columns Without Changing Combined Size Windows 8 Adjust Two Columns Without Changing Combined Size Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

Open the sub in VBA editor. In next row of code after 'Dim', click on left border in editor window - a bullet appears which marks the row where you start debugging the code;
Start the sub. The code stops at bullet;
Press F8 to continue with code row-wise until you get the error message. The row where you got the error is problematic one. You can select any variables or expressions there, right-click on selection, and select Add Watch from dropdown list. When the selection is a valid expression, then the selection is added into Watch window at bottom of VBA Editor window, and the value of selection is displayed there. This allows you to analyze all components for this code row - some of them must be source of error.
Reply With Quote
  #8  
Old 04-13-2020, 06:31 PM
BobBridges's Avatar
BobBridges BobBridges is offline Adjust Two Columns Without Changing Combined Size Windows 7 64bit Adjust Two Columns Without Changing Combined Size Office 2010 32bit
Expert
 
Join Date: May 2013
Location: USA
Posts: 700
BobBridges has a spectacular aura aboutBobBridges has a spectacular aura about
Default

What Arvi said, beno—but also, let us know what error you got. And when you get the error message you are probably presented with a choice of End or Debug buttons; if you choose Debug, you'll see the statement highlighted that is the cause of the error. (Well, usually it's the cause. Sometimes it's just where VBA was when it noticed the error.) That would help us help you figure it out, too, if it isn't obvious to you by this time.

If you have trouble figuring out what Arvi was trying to tell you about "clicking on the left border", you can accomplish the same thing by writing the command "Stop" anywhere in the program.

Once you know what's causing the problem, and have it fixed, we can come back to that "subroutine wrapping" thing you were talking about; I'm not sure what you mean, but I'm all about making subroutines where you have the slightest excuse for it.
Reply With Quote
  #9  
Old 04-13-2020, 07:56 PM
beno_83au beno_83au is offline Adjust Two Columns Without Changing Combined Size Windows 10 Adjust Two Columns Without Changing Combined Size Office 2019
Novice
Adjust Two Columns Without Changing Combined Size
 
Join Date: Apr 2020
Posts: 5
beno_83au is on a distinguished road
Default

Thanks Bob and Avri, I wasn't aware of that as a debugging method, although I'm no longer getting error messages now.

Quote:
Originally Posted by macropod View Post
Did you try the code I posted?
And yes, I did try the code, and it works to set both columns when I enter a width for Column B. So I have been trying to re-write that into a loop to resize C when I drag to resize B (p.s. Sorry if I'm not picking up on what you're telling me). I wen't on a bit of an adventure last night with it, and now with a fresh head I think I made have done things a little better...... So here's what I've got and then where I'm stuck:

Code:
Sub CommandButton1_Click()
Dim totalWidth As Single, columnBWidth As Single, columnCAdjustedWidth As Single
totalWidth = Columns("b").ColumnWidth + Columns("c").ColumnWidth
Do While Selection.Columns.Count = 2
    columnBWidth = Columns("b").ColumnWidth
    columnCAdjustedWidth = totalWidth - columnBWidth
    Columns("c:c").ColumnWidth = columnCAdjustedWidth
    Application.Wait(Now + TimeValue("0:00:01"))
    MsgBox ("Column adjusted")
Loop
MsgBox ("Ended.")
End Sub
When I run this, Excel seems to freeze until the "Column adjusted" message box appears (~1 second). The problem I'm having now is that with Excel frozen, I can't drag to resize anything obviously, and removing the MsgBox command just means I've got to force close to try again. Is it possible to run the loop in the background while I'm working on the sheet? Or should I be using one of the events that I've since discovered instead (that I had even less success with last night)?
Reply With Quote
  #10  
Old 04-13-2020, 08:59 PM
BobBridges's Avatar
BobBridges BobBridges is offline Adjust Two Columns Without Changing Combined Size Windows 7 64bit Adjust Two Columns Without Changing Combined Size Office 2010 32bit
Expert
 
Join Date: May 2013
Location: USA
Posts: 700
BobBridges has a spectacular aura aboutBobBridges has a spectacular aura about
Default

I see something in your program that makes me afraid to try it for myself, but I think I can describe it and you tell me whether I'm mistaken.

1) First step makes sense; you're getting the width of the two columns:
totalWidth = Columns("b").ColumnWidth + Columns("c").ColumnWidth

2) I don't understand this part: You start a loop, and tell it to go on doing it over and over as long as your selection takes in cells in exactly two columns. But I don't see that anything inside the loop changes that selection, so the program will go on running, redoing the loop again and again.
Do While Selection.Columns.Count = 2
Loop

3) What you do inside the loop looks like it started out to make sense: You want to adjust the width of column C to make B+C come out to the desired width. Looks to me, though, as if you made it actually come out to the same width it already was. That's because you're using the actual width instead of the desired width:
columnBWidth = Columns("b").ColumnWidth
columnCAdjustedWidth = totalWidth - columnBWidth
Columns("c:c").ColumnWidth = columnCAdjustedWidth

4) I also don't get what the Wait statement is for, but I don't think it's hurting anything. Excel seems to freeze, as I see it, because it's running the loop over and over again.

Here's what I would do:
Code:
Sub CommandButton1_Click()
  Columns(3).ColumnWidth = 539 - Columns(2).ColumnWidth
  End Sub
Nothing wrong with using Columns("B") (or is it "B:B"?) if you want; I'm used to usng numbers, is all.
Reply With Quote
  #11  
Old 04-13-2020, 09:22 PM
beno_83au beno_83au is offline Adjust Two Columns Without Changing Combined Size Windows 10 Adjust Two Columns Without Changing Combined Size Office 2019
Novice
Adjust Two Columns Without Changing Combined Size
 
Join Date: Apr 2020
Posts: 5
beno_83au is on a distinguished road
Default

Hey Bob. First up, the wait was just an experiment to see if reducing the number of cycles would stop the freezing, but it didn't help. As you said, I don't think it hurt either.

For the loop, I was under the understanding that the condition would be analysed each cycle, so that when I de-selected the 2 columns it'd return false. But I've been informed that this is not the case and that I'd need to set a variable and check it inside the loop.

For the widths, the numbers checked out IIRC. So, subtract B width from the desired total width to give C's width, then set C.

And I liked using B/C to refer to the columns themselves when I was not using a Selection. Otherwise, I'd use numbers for the index positions.

HOWEVER, after talking to a mate over Discord, he did me up some code for a SelectionChange event that works nicely. The only requirement is that after resizing I just need to select a cell to force the change. So that works well enough for now:

Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim tWidths As Double
    tWidths = 75.57
    If Not Columns("B").ColumnWidth + Columns("C").ColumnWidth = tWidths Then
        Columns("C").ColumnWidth = tWidths - Columns("B").ColumnWidth
    End If
End Sub
I think this seems to be the best that can be done for what I was trying to do, as there's no event that covers resizing (as far as he knows anyway).
Reply With Quote
  #12  
Old 04-14-2020, 07:36 PM
BobBridges's Avatar
BobBridges BobBridges is offline Adjust Two Columns Without Changing Combined Size Windows 7 64bit Adjust Two Columns Without Changing Combined Size Office 2010 32bit
Expert
 
Join Date: May 2013
Location: USA
Posts: 700
BobBridges has a spectacular aura aboutBobBridges has a spectacular aura about
Default

Yeah, I agree; I wasn't able to find any event that might be triggered by resizing columns either.

There's nothing wrong with triggering it via the Selection_Change event, as far as I can see, but if you want to get fancy you could use Worksheet_BeforeDoubleClick. That way it isn't invoked every time you move the cursor or innocently click on a cell; it runs each time you double-click on a cell. That means you have to work a little harder to invoke the program, but by the same token it means it isn't running as often when not needed. Just a thought, not necessarily a recommendation.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Adjust Two Columns Without Changing Combined Size How to Adjust Every Header Section Size at Once? CrossReach Word 4 03-13-2016 10:49 AM
Adjust Two Columns Without Changing Combined Size How do I adjust the size of the printed slide Suppy PowerPoint 2 01-27-2015 02:15 AM
How to adjust Image size and other question arjfca Word VBA 1 12-05-2014 02:00 AM
Adjust Two Columns Without Changing Combined Size Powerpoint 2013 doesn't adjust size on external display mejnik PowerPoint 1 04-04-2014 09:12 AM
Adjust Two Columns Without Changing Combined Size Adjust Window size at open Gary Drumm Windows 2 03-31-2014 08:22 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 11:08 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft