#1
|
|||
|
|||
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. |
#2
|
||||
|
||||
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... |
#3
|
|||
|
|||
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.
|
#4
|
||||
|
||||
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] |
#5
|
|||
|
|||
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 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 :) |
#6
|
||||
|
||||
Did you try the code I posted?
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#7
|
|||
|
|||
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. |
#8
|
||||
|
||||
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. |
#9
|
|||
|
|||
Thanks Bob and Avri, I wasn't aware of that as a debugging method, although I'm no longer getting error messages now.
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 |
#10
|
||||
|
||||
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 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 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 |
#11
|
|||
|
|||
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 |
#12
|
||||
|
||||
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. |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
How to Adjust Every Header Section Size at Once? | CrossReach | Word | 4 | 03-13-2016 10:49 AM |
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 |
Powerpoint 2013 doesn't adjust size on external display | mejnik | PowerPoint | 1 | 04-04-2014 09:12 AM |
Adjust Window size at open | Gary Drumm | Windows | 2 | 03-31-2014 08:22 AM |