I have a workbook with 8 sheets, one of which is the "main" sheet that holds all of my user data for logins at my job (I'm in IT). There are about 4300 users and 23 fields for each user, so there are 4300 rows in the sheet. Other sheets in the book are important, but not as much as this one.
The workbook has a total of over 1000 lines of VBA code right now, as I'm building a tool that finds users, adds users, modifies users, etc. (I know, Access would be smarter for this, but for some reason they want me to use an xls). Most of this code works with the sheet that I'm having the problem on.
This ONE sheet has seemed to disable the functionality of the scroll wheel, and I can't use Page Up or Page Down while in the sheet either. Also, if I select cells with VBA (Sheet2.Range(<range>).Select), it will select the cell, but it will not draw focus to that cell on screen (the sheet will not move at all). The other 7 sheets in the workbook have all regular functionality.
This just started happening a couple days ago, and I've been building the code for about a week, so I'm wondering if it's something in the code...?
I am having problems with an Excel workbook I am working on. I have a sheet that has a number of controls added to the top of it. Two of these controls are listboxes. My problem is, if I am in the listbox navigating through it, and I accidently move the Mouse Wheel, Excel crashes and I get the usual, "Send Error Report" form.
I have seen that there are other people having this problem and that there are a number of solutions out there but my problem is a bit different. I have a few forms with listboxes on them, and while mouse wheel scrolling doesn't work, it certainly doesn't crash Excel. It only seems to happen when I have a control permanently added to the sheet. Is there any event handler for mouse wheel scrolls or anyway to disable them through VB?
I have seen some code around that allows scrolling in listboxes using the mousewheel. I have also seen some code here on the forums about scrolling through a combobox embedded in a workbook. I have tried adapting both of these to suit a combobox on a userform but I cannot get either to work.
Here is the code that I used for the embedded combobox; I added it to the base code for the userform
I then added the following code to a standard module as stated in the forum post;
In the first section of the code there is a msgbox that never gets shown so I don't think code is recognizing the mouse wheel at all.
The second set of code that I used was set up to work with a listbox and it works perfectly with a listbox control. The problem is I cannot adapt it to suit a combobox and if I use a breakpoint to see what is happening Excel freezes. The following code is added to the userforms code;
Then in a standard module I added the following code;
I added this section of the code;
I think this should only work on a Combobox but a Combobox does not appear to support the TopIndex value. Is there a way this can be setup to work with a userform combobox? Sorry about the huge amount of code.
I'm using Excel 2007. When I try to scroll with the mouse wheel, it doesn't do anything. If I hold down the control key and scroll with the mouse, it zooms in and out. So that works fine, but I can't do the basic scrolling up and down the document with the mouse wheel. There is no "Tools, Options" menu in 2007 so I don't even know where to find this type of option. The options available from the Office button are completely different.
In the menu via TOOLS-->OPTIONS-->VIEW I am trying to remove the scroll bars from just one worksheet but when unticked, this option affects all of the worksheets in my workbook. Is there a way to just nail this selection down to one sheet via properties?
I have a worksheet with a column("A:A") of about 1000names. The user will start at the top and work down, entering stuff into other columns for some ofthe names. My need is: How do you automatically scroll, with code, say row 48to the top of the worksheet when the user reaches row 48 with?.
I have an excel sheet embedded in a Powerpoint presentation, but I have the problem that users are editing the sheet and accidently or unknowingly scrolling around. This results in the window resizing itself when the exit the object and messing up the appearance of the presentation. Is there a way of easily preventing scrolling (either via VBA or other)? I can obviously hide the scroll bars but that does not prevent the use of the mouse's scroll-wheel. I have also already hidden all unused columns and rows so that they cannot oversize the window.
able to keep a chart stationary while scrolling through a worksheet entering data. A few points:
1) 'Freeze pane' wouldn't work for me since the chart is too large and I'd prefer to have the data sheet unfrozen for easy perusal.
2) I saw mention of opening a separate window with the chart in it, while having the workbook window aligned next to it. I don't know if that can work, but I'd also prefer to just have the chart on the worksheet and stationary.
When I limit the scrolling area in a worksheet, and it works there and then, when I close and reopen the whorkbook, the limiting does not work.
This is how I do it:-
I Right-click the sheet tab for the sheet which I want to place a limit. In the resulting Context menu, I choose View Code. The VBA editor appears and asks for the password to view the code. I keyin the password
I then set in my limit as B2:O25
Then I close the VBA Editor and display the worksheet where I try and I find it working.
I have a datatable with data from 2010 and 2011. The worksheet will not scroll at all. It looks like it is scrolling at the side where the scroll bar is but the worksheet does not move. I can move the tab up or down but the page/worksheet doesn't move at all. If I activate the filter and select only the year 2011 in the drop down box for the Date column, it scrolls fine. But if I check "All Dates" or just "2010" from the drop down box on the Date column the page/worksheet will not scroll. I can select a certain month from the drop down box for 2010 and it will show the data (about 25 rows) but I cannot scroll. When I have just 2011 selected from the filter it all works fine. I'm not real swift with Excel. It was working fine until today.
I'm using the following code. It works fine when it comes to selecting the cell. However, I'm not sure how to bring the view of the worksheet such that the selected cell is in the middle of the sheet. I've the data horizontally laid out from column A to column BM. So, when someone makes a change in Sheet2 I need to point to the corresponding column in Sheet1 such that it is in the view.
Code: Private Sub Worksheet_Change(ByVal Target As Range) Dim CellName As String CellName = Range("A1").Value
I have two basic questions regarding the Excel program/UI
I'm currently using Excel 2000, One annoying thing I find with this version is the way the screen doesn't follow along with my scrolling. ie: when I use the vertical scroll bar on the main window, it only updates once I release... not like a typical window which follows the movements of the scroll curser. Is this typical for later versions?
I would update this program just for this feature alone!
Also, a final question.... Does anyone know if there are any plans to update the handheld (pocket pc) version of Excel? The factory installed program is incredibly limited.
I used to have cells in the extremes of my worksheet filled which resulted in the scroll bars being stretched very thin. Since then, I am only using the top-left part of the worksheet but it is difficult to scroll through since the scroll bars are still stretched. How do I bring them back to normal?
I am using the toolbox/ properties method to restrict the scroll area in the sheets of my workbook. However, for some sheets when i keep the right hand arrow pressed, the screen will not scroll to the last column. (i.e. the screen display will not move with the selected cell box)
I do not have any information about exel but there is an urgent need to program.The numbers on a roulette wheel automatically want to calculate the distance between. There are 37 numbers on the roulette wheel and Sequences matter of form
I've written a program that list all the occurences of a given name in a specific cell from all worksheets in the active workbook. The list is placed in a listbox and the first listing is selected with ListBox1.Selected(0) = True. Additionally the worksheet of the first listing is activated.
My question is now how do I either scroll through each name in the list box and activate the "current" worksheet. I assume that I'll need to address scrolling up/dowm and clicking of any particular name on the list.
So my macro goes through about 30 workbooks--opens them, updates the links, saves, closes. It would work flawlessly except I have to stop and click "continue" when this message pops up for most of the workbooks:
"This workbook contains one or more links that cannot be updated....etc."
About 90% of the workbooks have bad links somewhere (I think in the insert>names...but they're not my workbooks so i can't delete all the names). Since I can't find the bad links, I would like a visual basic script that will automatically click "continue" everytime so that the macro runs its course without me having to do anything. Does anyone know how to do this?
I have inherited a large spreadsheet. When it opens I get a warning message "This workbook contains one or more links that cannot be updated". When I click "Edit Links" the dialoge tells me that it is looking for a file in a folder called "All Daily Tasks". now I know that this folder no longer exists. I have found all the cells that had formulae refering to it, and pointed them in the new, correct folder, but I still get the warning message. I have cleared all Named Ranges, so that isn't the problem. And I have done a search for "All Daily Tasks", but this has come up blank. I know I can break the link, but I want to know where the link is, in case it needs updating. How do I find the cell with the "link that cannot be updated" if the search facility can't locate it?
I have a file that's supposedly taking some of its values from another file. I say supposedly because when I follow the directory path, its not in there. I think I did find the file its referencing (same name, now in a subdirectory of the original reference). But I changed a value in the file being referenced and it didnt change anything in the active sheet.
I imagine the previous paragraph is a bit confusing so let me say it in another way.
I'm working on a file named A. In that it refers to a file named B, in my DocumentsFinancial directory.
In the DocumentsFinancial directory there is no file named B. However I did find a file named B in DocumentsFinancialWork.
I assume this is what A is referring to. But when I changed the relevant value in B (which does equal the value in A), the value in A didnt change.
I do remember in certain exceptions, Excel files have to be refreshed, I think when manual calculations are on. I'm not sure how to check for that, but I did go to the Data Tab and hit Refresh All.
Function MultLookup(Rng As Range, lookup_value As String, col_offset As Integer, Optional num_return As Integer) As Variant Dim myArray(), cnt, cell For Each cell In Rng If cell.Value = lookup_value Then cnt = cnt + 1 ReDim Preserve myArray(1 To cnt) myArray(cnt) = cell.Offset(0, col_offset).Value End If Next cell
If num_return = 0 Then For i = 1 To cnt If i = 1 Then MultLookup = myArray(i) Else MultLookup = MultLookup & ", " & myArray(i) End If Next i ElseIf num_return > 0 And num_return
This file pulls totals from external links. The way I have it set up is that every month our log is saved to a naming scheme involving the date, then a fresh log is created for the current month. So, for each month I have "log mm-yyyy".
Now, the chart file is a thirteen month rolling chart (meaning every month it must show the thirteen most recent month's totals). I have created a macro to automatically update the links, based on an input in cell A:1 (meaning you can enter a date in the past and view the thirteen months previous). The problem is that we have only been doing the log for 10 months. So, when the macro runs to update the links, and it gets to months 11 through 13, it pops up a window for the user to manually browse for the missing(non-existent) files. There are quite a few cells that contain links to various information on these logs. I get a prompt for each cell and have to click "cancel". Once I have clicked cancel through all of the broken links, then everything works great.
My question (after all that) is, is there a way to suppress the prompt for the user to search for the missing file? Like I said, this chart is for management, and they shouldn't have to click cancel a dozen times just o look at a chart. If the file doesn't exist, then just break the link.
Obviously once the next three months are over, this problem will disappear, but in the mean time I need a solution.
I need to know what driver led a given lap of a given race (Col. G). Attached is my attempt. Something to consider is I need a formula that can be pasted down Column G as from track to track, from race to race the # of laps will not be the same. The number of drivers that led a given race will not be the same.
I have an array formula in cells of Column G for the # of laps in a given race (i.e. if a race was 100 laps it would go from G3-G100). I ran it down to 500 just to cover myself.
Öit kinda works, but for some reason when it comes to lap 251 it gives me a #N/A error. It gives me the correct driver for lap 250 & 252 and all other laps but not lap 251. Also, a minor inconvenience is that I have to put a space after the first number & if at all possible I donít want to have to do that.
I would love a macro where all I need to do is paste Column A, press a command button, & the macro does everything for me but thatís probably asking a lot. I know how to record a macro but donít know anything about VB.
Column D & F have to be formatted as text. It has to be text format or when I cut Column B to Column D it changes some numbers to date (i.e. 1-2 becomes January 2nd).