Of cource, they could also change the text of the macro above but that should be easier to notice.ĮxcelWideDrop_v1.zip (27k) ( PGP signature) It's always possible that someone hacks my web server and puts up somethig malicious.
#Range resize excel vba zip file
I have signed the zip file with PGP to confirm it comes from me and has not been tampered with but if you have no way of verifying the signature then be careful. Note that downloading Excel spreadsheets, especially ones containing macros, can be dangerous. In case it is useful I have provided an example spreadsheet with the button already hooked up to the macro. Res = MoveAndResizeWindow(WndCombo, WndBar, 0, 0, 0, 600) Res = SendMessage(WndCombo, 352, 200, 0) ' 352 = CB_SETDROPPEDWIDTH WndCombo = FindWindowEx(WndBar, 0, "ComboBox", vbNullString) WndBar = FindWindowEx(WndExcel, 0, "EXCEL ", vbNullString) WndExcel = FindWindow("XLMAIN", Application.Caption) MoveAndResizeWindow = SetWindowPos(hwnd, 0, rct.Left, rct.Top, rct.Right - rct.Left, rct.Bottom - rct.Top, cSWPFlags) MoveAndResizeWindow = ScreenToClientRect(hwndParent, rct) MoveAndResizeWindow = GetWindowRect(hwnd, rct) X As Long, y As Long, w As Long, h As Long) As LongĬonst cSWPFlags = 20 ' 20 = SWP_NOACTIVATE|SWP_NOZORDER Private Function MoveAndResizeWindow(ByVal hwnd As Long, ByVal hwndParent As Long, _ ScreenToClientRect = ScreenToClient(hwnd, p2) ScreenToClientRect = ScreenToClient(hwnd, p1) Private Function ScreenToClientRect(ByVal hwnd As Long, ByRef lpRect As W32RECT) As Long Private Declare Function ScreenToClient Lib "user32" (ByVal hwnd As Long, lpPoint As W32POINT) As Long Private Declare Function GetWindowRect Lib "user32" (ByVal hwnd As Long, lpRect As W32RECT) As Long (ByVal hwnd As Long, ByVal hWndInsertAfter As Long, _īyVal x As Long, ByVal y As Long, ByVal cX As Long, ByVal cY As Long, ByVal wFlags As Long) As Long Private Declare Function SetWindowPos Lib "user32" _ (ByVal hwnd As Long, ByVal wMsg As Long, _īyVal wParam As Long, lParam As Any) As Long Private Declare Function SendMessage Lib "user32" Alias "SendMessageA" _ (ByVal hWnd1 As Long, ByVal hWnd2 As Long, _īyVal lpsz1 As String, ByVal lpsz2 As String) As Long Private Declare Function FindWindowEx Lib "user32" Alias "FindWindowExA" _ (ByVal lpClassName As String, ByVal lpWindowName As String) As Long Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" _ (Drop-down controls are a bit weird since they have two sets of dimensions, one for the collapsed control and one for the expanded drop-down list.) Private Type W32RECT If not then here's a quick explanation: It finds the drop-down window control, resizes it horizontally (sending it a CB_SETDROPPEDWIDTH message) and then resizes it vertically (by resizing the window itself).
#Range resize excel vba code
If you are familiar with Win32 then the code should be obvious to you.
That's what I bind the button in my sheet to. The function to run is WideDrop right at the end. Here's a screenshot showing the drop-down, before and after: This will resize both the toolbar field and the drop-down (unlike my macro which only resizes the drop-down).
Just point to the gap between the field and the formular bar and you'll see a resize cursor, then click and drag to the right. It seems that Microsoft realised there was a problem and finally, after all these years, made it possible to resize the named-range field with the mouse in Excel 2007. That way if anything goes wrong you can simply stop clicking the button.Įxcel 2007 (and above): If you're using Excel 2007 or above then you don't need this macro and it won't actually do anything anyway. If you want to use the macro then I recommend binding it to a button in your sheet which you can click manually rather than setting it to run automatically when your sheet is opened. I have used it a lot it without seeing any problems but your experience may be different.
This is not supported by Microsoft and could cause something to break. Warning: This macro makes the drop-down larger by directly resizing Excel's private windows. Change the numbers 200 and 600 in the last two lines, ignoring the "End Sub" line. You can edit the macro to change the size if you wish. The VBA macro provided below will resize the drop-down to something larger and more useful.
This can cause big problems when editing sheets with a lot of ranges. In versions before Excel 2007 this list is ridiculously small, both in width and height. In Excel, on the left of the formula bar, there is a drop-down list for the named ranges in the current sheet.
0 kommentar(er)
