workbook / worksheet change event

TheMSsForum.com: The Microsoft Software Forum

The MSS Forum ‹ Excel Programming
- Archive
  - Biz
  - MCSE
  - CRM
  - Drivers
  - Framework
  - ADO
  - ASP
  - Compact
  - Forms
  - Dotnet
  - C#
  - VB
  - FontpageGen
  - Excel
  - WorkSheet
  - Exchange
  - Setup
  - Fox
  - Fontpage
  - ASP
  - IIS
  - Entourage
  - Money
  - Messanger
  - PocketPC
  - Powerpoint
  - Project
  - Publisher
  - Excel
  - VB
  - Security
  - Portal
  - Services
  - SQLServerDev
  - SVCS
  - SQLServer
  - VB
  - VC
  - MFC
  - ExcelGen

- Previous
  - 1
    - Pulling data from 3000 files Hi All, I'm going crazy try to copy and paste data from 3000 files into 1 file. Is there a way to automatically open a file, copy columns 1 -5 and then append it to the file where I want it all placed? Thanks in advance... Tag: workbook / worksheet change event Tag: 981297
  - 2
    - My code enters break mode but there are no breakpoints For some odd reason my code is acting like there are breakpoints in a handful of locations. I tried exporting the module, deleting the old excel file, creating a new excel file, importing the module, and running it. Same thing happened. Then I copied the code into Word, deleted the Excel file, re-created it, and pasted the code into a new module. Same problem. Any ideas??? I'm using Excel 2007. Tag: workbook / worksheet change event Tag: 981287
  - 3
    - Macro to show/hide rows I need to use a single cell to show/hide other rows. For instance if you put a "6" in cell B1 it would show the next 12 rows and hide the rest. If you put in a "10" it would show the next 20 rows and hide the rest, etc. I'm new to writing macros, sorry! Tag: workbook / worksheet change event Tag: 981276
  - 4
    - Variable Input I have to use, what I thought was, a simple variable input, but it's turning out to be not so simple. Basically I loop through all sheets in a book, as long as the sheets <> "Sheet1". Then do some basic formatting and some simple math. Everything works fine except for this part: Chng = Range("F2") and this part: c.Offset(, 19).Value = "=(IF(RC[-20]="&Chng&",RC[-18],0)+IF(RC[-15]="&Chng&",RC[-14],0))*RC[-3]" I have names in column F of each sheet, which are all the same on each sheet, but different names are on different sheets. All I need to do is figure out the value in F2 on each sheet and use that in my IF function. I thought I could just assign the value in F2 to the variable Chng and then just use that variable in my IF function. Excel has other thoughts and it is telling me that I can't do that. Why? All code below: Sub Math() Dim lastRow As Long Dim c As Variant Dim sh As Worksheet Dim myA As Range Dim lastYRow As Long Dim sumRng As Range Dim Tot As Double Dim Chng As Variant For Each sh In Worksheets If LCase(sh.Name) <> LCase("Sheet1") Then sh.Activate With sh .Rows(1).Font.Bold = True Chng = Range("F2") lastRow = .Cells(.Rows.Count, "F").End(xlUp).Row For Each c In .Range("F2:F" & lastRow) If c.Value <> "" Then c.Offset(, 19).Value = "=(IF(RC[-20]="&Chng&",RC[-18],0)+IF(RC[-15]="&Chng&",RC[-14],0))*RC[-3]" End If Next c lastYRow = .Cells(.Rows.Count, "Y").End(xlUp).Row Set sumRng = .Range("Y2:Y" & lastYRow) Tot = Application.WorksheetFunction.Sum(sumRng) 'MsgBox Tot .Cells(lastRow + 1, "Y").Value = Tot '.Rows(2).Delete End With End If Next sh End Sub Any help is appreciated. Regards, Ryan-- -- RyGuy Tag: workbook / worksheet change event Tag: 981275
  - 5
    - Macro Error Help!! Please I need help in resolving this error "Sub or function not defined". I tried using Ron's code http://www.rondebruin.nl/copy3.htm and i get error. Please help me in identifying what am doing wrong. all responses appreciated. Sub RDB_Module1() ***** This get highlighted Dim myFiles As Variant Dim myCountOfFiles As Long myCountOfFiles = Get_File_Names( _ ****this also gets highlighted I believe these two function are not properly called or referenced to. HELP Thanks Yossy Tag: workbook / worksheet change event Tag: 981273
  - 6
    - Count function? If Col A contains a text variable and column B contains a numerical variable associated with the text variable, what is the simplest formula I can put into a single cell that will count the number of occurrences of the numerical variables in Col B associated with the text variable in A? Tag: workbook / worksheet change event Tag: 981272
  - 7
    - Appending Contents Of Multiple Spreadsheets Into One Hello. I would like to open up a new spreadsheet and append rows 2 - EOF from six other spreadsheets into that new one, be prompted for the name to save it with, then save and quit. Would someone be kind enough to offer some VBA code to do this with? Thank you, Sheldon Potolsky Tag: workbook / worksheet change event Tag: 981259
  - 8
    - What's the API call to kill the KB buffer? I don't want keystrokes typed in advance of my UserForm appearing to remain alive and execute. Anyone know the API call (which I assume this requires) to flush the keyboard buffer? Thanks much. *** Tag: workbook / worksheet change event Tag: 981247
  - 9
    - ListBox Displaying With Some Items Already Selected In 2007 I have an application that I have been using without problems since Office 2000. It has a list box, which is populated using the command: cyo_dialog.sel_box.RowSource = "product_list" It then goes on to ensure that all the items are deselected with the code: For a = 0 To (cyo_dialog.sel_box.ListCount - 1) cyo_dialog.sel_box.Selected(a) = False Next a cyo_dialog.sel_box.ListIndex = 0 Since I started using this on 2007, for some unknown reason two of the first three items come up selected (normally item 1 with either 2 or 3) , and are then not deselected by the code that follows. I have found 2007 to be very buggy and inconsistent in its running, but I can see no reason why this should now be happening. Thanks in advance. Tag: workbook / worksheet change event Tag: 981246
  - 10
    - Checking if a cell contains a string that is not case sensitive I am trying to count how many times a specific string "abc" forms part of a cell in a spreadsheet. For example abcgfd, abcdse, efgabc would be vaild and I would like to count these abdcef, bcadef, cbaeds would not be valid and I would not want to count these. Can anyone help point me in the right direction? Thanks. Tag: workbook / worksheet change event Tag: 981244
  - 11
    - TreeView's pos on UserForm changes by itself. Until I complete the excruciating process of finding out how to disable screen updating while pruning a TreeView (deleting & moving nodes), I hit on the idea of setting TreeView.Visible = False, then = True again when processing's done. When I do, the TV's re-shows in a DIFFERENT position on the UserForm than before. (It has shifted to what seems like the U.L.-most corner.) NOW what's going on? Thanks. *** Tag: workbook / worksheet change event Tag: 981243
  - 12
    - updating pivot table data via external xls file which is replaced hi, the problem with my code for updating the table is that it works on my computer but if i try it on another one the pivot table itself isn't updated but a new worksheet created with an empty pivot in it ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _ "U:\xyz\xyz_Shared\test\TestLines.xls!R4C1:R50000C38" The file from which the table should get it's data is called TestLines.xls Tag: workbook / worksheet change event Tag: 981240
  - 13
    - Numeric data Dear all Pleasae guide me to find out numeric data in cell where the cell contains both aplha & numeric value. Like i want to take out pincode from one single cell where in the location and pincode is mentioned in a single cell Eg ( Mumbai 400018 ) Serch Result shud b 400018 Thanxs in advance Tag: workbook / worksheet change event Tag: 981235
  - 14
    - Cell references and merged cells Is there an explanation somewhere of how to handle column references when there are merged cells in another row? For example: Private Sub TestOffset() 'Expect references to columns A, B, and C. 'Get references to columns AB(merged), C, and D, if there are merged cells in other rows Dim r As Long Dim rng As Excel.Range Range("A1").Activate Set rng = Range("A1") With rng Debug.Print .Address .Value = 1 .Offset(0, 1) = 2 .Offset(0, 2) = 3 For r = 0 To 12 Step 6 Debug.Print .Offset(r, 0).Address, .Offset(r, 2).Address, .Offset(r, 4).Address, .Offset(r, 6).Address Next r End With Range("A20").Activate Set rng = Range("A20") Range("A20:B20").Merge With rng Debug.Print .Address .Value = 11 .Offset(0, 1) = 12 .Offset(0, 2) = 13 For r = 0 To 12 Step 6 Debug.Print .Offset(r, 0).Address, .Offset(r, 2).Address, .Offset(r, 4).Address, .Offset(r, 6).Address Next r End With End Sub Tag: workbook / worksheet change event Tag: 981232
  - 15
    - Excel 2007 SP1 VBA Help I have created a WortArt object in Excel programatically using VBA and using the preset text effect style msoTextEffect3. Unfortunately, this creates text with the color red. Now I can right click on the word art and change the color of the text to whatever I want. But I cannot find a way to do this programmatically in VBA. Also, trying to use Macro recording is useless for this. It looks like macro recording is broken in Excel 2007. Any help grateful Wheeley Tag: workbook / worksheet change event Tag: 981230
  - 16
    - Help to speed up this macro I have the following macro that runsa a UDF to delete blank rows (every other row). The formula is inserted in cell E11 and copied down for for each line where Col B is not empty. It works just fine, however, there are only 100+ lines and it runs very slowly. Is there something Ican do to speed it up? Sub BudgetJobNo() ' Sheets("Budget").Select Call RowsJoinData(2) '<where column 2 (B) is blank for every row to delete Application.DisplayAlerts = False Application.ScreenUpdating = False JobNo = "=IF(ISERROR(LEFT(Budget!B11,FIND("" "",Budget!B11)-1))=FALSE,LEFT(Budget!B11,FIND("" "",Budget!B11)-1),"" "")" Range("E11").Value = JobNo 'And then copy them down to the last line. Range("e11:e11").Select Selection.Copy CR = 11 Do While Cells(CR, 2) <> "" Range(Cells(CR, 5), Cells(CR, 5)).Select ActiveSheet.Paste CR = CR + 1 Loop Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub -- Jim Tag: workbook / worksheet change event Tag: 981229
  - 17
    - How do you collapse a TreeView? I cannot seem to find even basic properties in VBA's TreeView. There is Node.Collapse, which collapses the immediate children of a single node. Gee, Beaver, that's swell. How do you collapse the tree? At the risk of being obvious, I mean: every node. Thanks much. *** Tag: workbook / worksheet change event Tag: 981228
  - 18
    - WM_SETREDRAW doesn't work unless...what? RM Smissaert kindly replied to my recent Q of how to disable screen updating in a TreeView: Private Declare Function SendMessage Lib "user32" Alias _ "SendMessageA" (ByVal HWND As Long, ByVal wMsg _ As Long, ByVal wParam As Long, lParam As Any) As Long Private Const WM_SETREDRAW = &HB *** SendMessage TreeView1.HWND, WM_SETREDRAW, IIf(EnableTheBugger, 1, 0), 0 (The 1 and 0 in my IIf are reversed from his example, which I presume is correct, 'cause it seems to work for me.) But this call fails, UNLESS (I found empirically) I, like, make the call, then show a MsgBox (which interrupts processing and "seeds" the screen first...?) Sort of like having to turn off the pilot before turning off your stove. What additional API call must I make if any to make WM_SETREDRAW work? *** Better yet, how do you do this in a native VBA way? What I'm doing is cleaning & pruning the tree--deleting & moving hundreds or even thousands of nodes. Each operation of which has to wait for my ancient video card, which I inherited from Johannes Vermeer, to repaint (in slow-to-dry Old Master oils) my screen. Thanks much. *** Tag: workbook / worksheet change event Tag: 981227
  - 19
    - Confrimation of copy paste operation Hi, In my application (C#, Office 2007), i am attempting to copy a range of cells containing data to a range on another worksheet. Upon copying the range I then copy a chart into a specific area on the second worksheet. The chart is resized and set up to represent a datapoint per row of data so the chart. Occasionally (and i mean occasionally, i cannot replicate the problem 100% of the time) the chart does not get copied to the second worksheet. I do this a number of times for various different table ranges. Sometimes there will be a number of these charts copied to the same output worksheet and i have encountered this issue where by only some of the charts copy and others don't. I generally use the following code to do the copy paste _chObj = (ChartObject)_excelDataWS.ChartObjects("ChartName"); _chObj.Copy(); _excelPresentationWS.Paste(_presTopLeftRange), paramMissing); I have a couple of questions on this problem. 1) Is there a benefit to using the following copy command?: _chObj.CopyPicture(XlPictureAppearance.xlScreen, XlCopyPictureFormat.xlPicture); 2) Is there any way to confirm that a copy/paste operation has succeeded and if not then to retry it? I'm really having no joy in diagnosing why this is happening. I get no exceptions thrown when the charts do not appear. Any help is greatfully recieved. Thanks in advance, G Tag: workbook / worksheet change event Tag: 981226
  - 20
    - Capture paste from Office Clipboard Hi, A bit of background first I have an event macro which I want to prevent from running if the user paste some info from the office clipboard into a cell as it triggers my events. The below code works if the data is copied from one section of the spreadsheet to another. Is there anyway to capture Ctrl + V using select case?. Select Case Application.CutCopyMode Case Is = False MsgBox "Not in Cut or Copy mode" Case Is = xlCopy MsgBox "In Copy mode" Case Is = xlCut MsgBox "In Cut mode" End Select FYI I also looked at the on key event but didn't want to add a new module to the workbook if I can avoid it Application.OnKey "^v", "Macro1" If I had to add a module I guess Chip site would be the best start?? http://www.cpearson.com/excel/vbe.aspx Any suggestions of tips would be appreciated Regards VBA Noob Tag: workbook / worksheet change event Tag: 981221
  - 21
    - Modeless Form help please I'm using a modeless form with a spreadsheet. The reason for making it modeless is that I want to be able to use the spreadsheet in the background and refer to the form from time to time. However, When the form is displayed it disables the wheel on the mouse (which is quite a problem for me)... 1. Is there a way around this? 2. What else is different when a modeless form is displayed? Thanks M Tag: workbook / worksheet change event Tag: 981219
  - 22
    - Can i link various text in a cell to a picture I would like to link text in a cell to a picture so when i select or write text in a cell different pictures will appear next to the cell the text is in. Tag: workbook / worksheet change event Tag: 981217
  - 23
    - Thanks to Stephen Bullen for fixing VBA's miserable UserForm. With the qualification that I'm only using Win 2000 and this might've been fixed, and if I understand UserForm correctly: The seeming fact, which I just learned, that the highest-level visual control in VBA, UserForm, by design fails to implement even the most basic Windows features of being resizable, min- and maximizable (and I have yet to see a ScreenUpdate), has got to be the most pathetic, half- assed thing I've seen out of Redmond in a long time. UserForm is not a FORM. It has neither the appearance nor functionality of a form. It has little more than dialog functionality. I mean, when's the last time you've seen something on your Windows screen that's missing the Min/Max/Restore buttons, for God's sake? While they were at it, why didn't they omit caption functionality? Or force UserForm to be modal? (Thank God they didn't, but now I wonder: why not?) But can someone answer: what do users do who have screen resolutions different from the programmer's, when the box he designed doesn't fit their screen? As happened to me today when I ported my NonUserForm from one PC to another. I had to change in the form size IN DESIGN MODE. And, how in the world can all you programmers write an *application* in VBA? I REALLY hope I'm seriously missing something in my understanding here, I really do. It sure wouldn't be the first time. In fact, crow is one of the dishes I eat most gladly. *** In the meantime (and I can't believe it's taken me this long to discover it), I took Peter T's advice and went to Stephen Bullen's fabulous Excel site: www.oaltd.co.uk/Excel/Default.htm where there are all kinds of goodies. Not least of which is FormFun.xls, which includes all the necessary hooks out to Windows to fix the miserable, decrepit piece of crap that is UserForm. [Rant alert!] I have said this for decades: Microflot's Soviet approach to things will be its undoing. Because soon a company is going to come along with a computer on which you don't click a button called 'Start' to shut the goddamned system down. And by Soviet, I mean: Refusing to fix design flaws and their thousands of points of failure to implement their own--excellent-- Windows paradigms in EVERY user control, on EVERY screen, in EVERY context across their ENTIRE product spectrum. Yet, accomplishing that feat between even the most intimate siblings of the Office family is absolutely beyond this company. Instead, where do they spend their computing resources? Migrating us to operating systems requiring multiple cores and multi-gigabytes of storage, so we can have the thrill of executing a few MISERABLE machine instructions to do the things we did pretty well on our old 1981 IBM PCs--like balancing our checkbooks--but with 3D graphics, animation, and an electrical draw of a couple hundred more watts. If you have shares in MS, I say: maybe don't keep them for long. *** Tag: workbook / worksheet change event Tag: 981209
  - 24
    - Excel 2007 - How do I ... bring data into an excel 2007 worksheet that is stored in a sql database table? The idea is to parameterize the "where" clause in the sql select statement limiting the result set. There are lots of examples in the literature with regard to bringing in data stored in cubes --- but not so much from data in sql table. The basic issue is I want bring in descriptive information about say a contract (things like description, signing date, etc). Cubes which most (all) of the literature deals with are dealing with aggregations of amounts or "measures" and not so much about descriptions. As always all suggestions are welcome. Mark Tag: workbook / worksheet change event Tag: 981199
  - 25
    - UserForm ShowModal Problem I have a UserForm that will not Unload correctly for some reason when ShowModal = True. I get a Run-time Error '91': Object Variable or With block variable not set. But when I change ShowModal = False then everything Unloads properly. I have 8 other UserForms that have ShowModal = True and they all Unload just fine. Anybody know why this happens? Thanks in Advance, Ryan Tag: workbook / worksheet change event Tag: 981193
- Next
  - 1
    - code to extract data from a website runs of XP but not Vista I am working on code to extract data from a website. I am using CreateObject("InternetExplorer.Application") to start IE. The code is far from working perfectly but when I moved the code from an XP computer to a Vista computer I canâ??t get past the â??Do Until .readystate = 4â?? statement. IE opens visible and I get the following error message: â??The object invoked has disconnected from its clientsâ?? I did not get this error on the XP computer. The code is the following: Sub Tester() Dim objIE As Object Dim allTabs, t, x As Integer Dim r As Long, s As String, c As Long Set objIE = CreateObject("InternetExplorer.Application") With objIE .Visible = False .Navigate "http://www.masters.org/en_US/scores/index.html" Do Until .readystate = 4 â??hangs on this statement on the Vista computer DoEvents Loop With .document Set allTabs = .getElementsByTagName("TABLE") For Each t In allTabs 'table with scores is always greater than 30 and only table that large on page If t.Rows.Length > 29 Then r = 2 For x = 1 To t.Rows.Length - 1 For c = 0 To 9 s = t.Rows(x).Cells(c).innerHTML ActiveWorkbook.Sheets("Info").Cells(r, c + 1).Value = s Next c r = r + 1 Next x End If Next t End With End With End Sub I have a feeling from looking up the error on the web that it may have something to do with the added security of Vista. Has anyone else run up against this problem? I see a lot of code samples here that use the same code to open IE that I do. -- russ Tag: workbook / worksheet change event Tag: 981192
  - 2
    - Automanipulating IE drop-down list in VBA/Excel I'm strictly a newbie in VB; I've been programming on the mainframe for decades but am only recently come to VBA, VBScript etc, so if you notice that I'm going about this all wrong, be sure and point me in a better direction. I have a spreadsheet that contains data from the web, and I'd like to make it smart enough to update itself when I tell it to. I've learned a little about the "Document" object model, and have gotten to the point where I can browse down through all the .Document.All items and figure out which ones I want VBA to manipulate. I can even fill in the values of web pages' text boxes and simulate a .Click on its buttons. But now I want to make a selection from a drop-down box. Let's say it has a list of states on it: I want my VBA program to pick out the correct state and convince the web page that I've chosen it. I've found the Option items that define the possible selections, and I see that if I find the correct one and change its Selected property to True, it shows up on the page correctly as though I selected it myself. But what the page doesn't do is proceed from that point as though I had clicked on the item - that is, it shows up in the drop-down selection box, but I can't get it to go forward from there. I've tried .Clicking on everything I can think of (might have missed one, though), but nothing seems to work. What am I supposed to do to get to the next step. If there's a FAQ on this, or another forum I should post to, just point me in the right direction. And if there's an easier way than using the Document object model - which wouldn't surprise me - feel free to point that out to me, too. Thanks. Tag: workbook / worksheet change event Tag: 981190
  - 3
    - Create a Macro with a List Hi Experts: > > I would like to build a macro with the List for multiple individuals that > will then enter the data from the List to the Summary Worksheet in the same > Workbook. This is the example of what I want to do.... > > List Sheet > > John Jones in Column A....123456 in Column B...ABCDF in Column C and 90.05% > in Column D > > Put that data on the Summary Worksheet in Columns B, C, D & E > > I would also like to remove the % sign in Column D > > Can anyone help me with this? I would appreciate any assistance you can > provide. I'm working on a new reporting document that is using VLOOKUPs....a > lot of them every month....I think the Macro would be more efficient, but I > need your help to build on. -- jeannie v Tag: workbook / worksheet change event Tag: 981188
  - 4
    - I am not sure Hello; I am trying to help someone, for which I am not sure there exists a solution. He has a .csv file that he is opening in Excel. The values, somehow, import into the correct conglomeration of data. More in a minute. Column A is a number formatted as text. Column B is a series of numbers, some formatted as text. Those that are not formatted as text, are separated by commas (Huh!!) and hyphens. The object is to concatenate the two values together. Example: A | B | C | | 1 | 357 |Argentina | | 345 |32,34,39 |Paraguay | 40-45 | | | 33945 |27-30,41, | USA |43, 45 | The data should finally look like this for USA (etc.) A | B 3394527 |USA 3394528 |USA 3394529 |USA 3394530 |USA 3394541 |USA 3394543 |USA 3394545 |USA Which column the new values end up in does not matter. I know how to do this fairly rapidly with formula's and a big enough worksheet. Then copy and paste the values to where I want them. I am wondering if this is even possible. I am not a big VB fan. I like to avoid them if I can, but I wonder if this can even be handeled by VB. Column B on one of the cells looks like this as an example. 264235, 264469, 264476, 264497, 264536-264539, 264543, 264581-264584, 264724, 264729, 264772-264773 There is no regularity to the number of digits. There is no regularity to the hyphenation location. There is no regularity to the format of the numbers (which I guess all could be changed to numbers and back to text for concantenation) and there is no regularity to the quantity of values in the cell. Is there any hope for this? Any help would be appreciated. Thank You Frank Pytel PS. I see a lot of posts here that look really nice. I am on the Office Online site. I know these posts can be viewed and edited at other sites. Can I set this up in Outlook so that I can Post and search. Kind of like Outlook Express? Thanks Tag: workbook / worksheet change event Tag: 981185
  - 5
    - How to execute macro based on a users computer settings Hello experts, I'd like to find a method/script to only execute an excel macro based on a users existing file listing on their computer ( or it could be a pre-existing setting; ie. their IP or MAC address). Ideally I'd like to have a macro check for the existence of a certain file (or password phrase encoded in the file) on the directory of a users computer, if YES then execute macro, ELSE don't do anything. This is all done in the background right before the event that usually triggers the primary macro, which is an ftp command line script. My guess is creating a batch file that executes in a windows shell and outputs a value that could be read, but I'm not sure if that's the best way to achieve what I want. I want to avoid using a pop-up form and password inside the excel file - this is currently what I already have. Thank you for your help, -Doug Jr. Tag: workbook / worksheet change event Tag: 981182
  - 6
    - How best to break up code I hit the "procedure too large" error and I can't seem to get past it. I'm well aware of the reason and I can tell you know that I've effectively crammed all the IFs and LOOPs into it that I can, so let's assume that the code can't be condensed further. I've spent half-a-day reading through this group and Googling and can't seem to make it all come together. So my question is, how do I create this "well structured code" when I have SO MANY declarations which need to be used across the different procedures? I don't want to pass all of my declarations as arguments as I feel like that would take forever. There has got to me a more logical way that I am missing. And functions are supposed to be small aren't they? In reading the vba help, it seems that declaring the procedure or perhaps the individual variables using "static" would allow them to persist but they don't. Does it make a difference if I put everything in separate modules? From what I've read that isn't necessary. I read somewhere in this group that someone had a procedure that held all of their variables and they just called it in each procedure. How would this have been done? And if the variable is changed in the first procedure that calls it, then how can I get that value to persist to the next? I know the first question is going to be, let me see your code. But because this is more of a conceptual question, I'm leaving it out for now. I'll gladly provide some as needed. Many thanks for any assistance. Shelton Tag: workbook / worksheet change event Tag: 981179
  - 7
    - VBA Additional controls Hi all, I wanted to insert a spreadsheet on a form in excel 2003. So I went to tools additional controal and added spreadsheet 11.0. When I try and use this in my excel 2007 it says it can not find the object . In the additional controls there is no spreadsheet 11.0 is there way to add it ? a.so how can i find out what all the other additional controls do. Thanks Tag: workbook / worksheet change event Tag: 981175
  - 8
    - Controlling how a second excel file opens I have an Excel workbook I use to aggregate data from other excel workbooks. My macros copy data from the secondary workbook and appends the data to the primary workbook. My question: Is there the capability to open up secondary workbook without the secondary workbook executing any of it's macros and not do any calculations? Is there a better way to pull data from another excel workbook? Links are not an option. Thanks, - Pat Tag: workbook / worksheet change event Tag: 981174
  - 9
    - Checkbox I created a Userform with several checkboxes and comboboxes. Once all the information is filled out, i hit the run button. I'm getting an error because it's not reading if the checkbox is marked or not, it's always read as unmarked. What is the correct way of coding this? Tag: workbook / worksheet change event Tag: 981162
  - 10
    - Running Macros Remotely (.tcl file) I am trying to set up an automated macro routine via a .tcl "tickle" file (scheduled by the windows scheduler) to execute at a set time each day. When I execute the .tcl file below it will open the excel file but it errors out when it tries to execute the macro Error Msg : The macro may not be available in this workbook or all macros may be disabled. I can run the macro with no issues manually from the excel file. 1) does anyone have any idea what might be wrong ? 2) is there any other way to automatically run the macros at a given time ? Thanks, Grant TCL script: set application [::tcom::ref createobject "Excel.Application"] $application Visible 1 set workbooks [$application Workbooks] set workbook [$workbooks Open "D:/Profiles/r33998/Desktop/Working Parts Tracker/Split Out Tracking System/TCL testing.xls" 0 FALSE 2 sourcing] $application Run "TCL testing.xls!protect_worksheets_" #$workbook Save $application Quit Tag: workbook / worksheet change event Tag: 981154
  - 11
    - Combo Box and smart key selection Hi I have a spreadsheet (windows xp, MS Office 2003) with several userforms. One of these has a combo box, populated from the following code: Private Sub UserForm_Initialize() Dim myArr As Variant With Me.cbStdOP .ColumnCount = 9 'columns A:I .ColumnWidths = "180;0;0;0;25;0;0;0;0" 'hide the last 8 columns End With With Worksheets("AutoEntry") myArr = .Range("a2", .Cells(.Rows.Count, "A").End(xlUp)) _ .Resize(, Me.cbStdOP.ColumnCount) End With Me.cbStdOP.List = myArr End Sub This shows in the combo box only the column A (description) and column E (numerical codes) entries from the 'AutoEntry' worksheet. The user scrolls thru these and selects from these choices in the combo box, and the resulting data is placed into another ws ('Operations'). The rows in 'AutoEntry' are sorted by column E (numerical codes). I know this is not the best way to do this. I would like the user to be able to type the first numbers of the codes and have the combo box go to that selection (there are > 1500 rows to scroll through on the AutoEntry ws). Ideally, there would be a radio button to search by description OR by numerical code, and the combo box would show the matches. Thanks for any guidance! cls Tag: workbook / worksheet change event Tag: 981153
  - 12
    - Working with Arrays I am trying to use an array to move a group of sheets from one workbook to a new workbook. I know how to move them ALL at once to a new workbook using: Sheets(array("Sheet1", "Sheet2", "etc.")).Move However, only want to sent select sheets (selected by another, working, section of code). Here is my code, so far: ' define and setup variables Dim create_array, all_graphs Dim offset_var As Long Dim array_counter As Long offset_var = 0 array_counter = 0 ' this is a list of all the sheets that CAN exist all_graphs = Array("1.1.1 - LD AN Consumption", "1.2.1 - AN Amounts", "1.3.1 - Total AN Consumption", "2.1.1 - Tank Temperature", "2.1.2 - Fudge Point", "2.1.3 - pH", "3.1.1 - Blender Speed", "3.1.2 - Hot Cup Density", "3.1.3 - Viscosity", "4.1.1 - Truck Cup Densities", "4.2.1 - PE105 Calibration - AN", "4.2.2 - PE105 Calibration - Emu", "4.2.3 - PE105 Calibration - FO", "4.3.1 - PE106 Calibration - AN", "4.3.2 - PE106 Calibration - Emu", "4.3.3 - PE106 Calibration - FO", "4.4.1 - PE105 - Scale vs. Load", "4.4.2 - PE106 - Scale vs. Load", "5.1.1 - Tank Temperature", "5.1.2 - Fudge Point", "5.1.3 - pH", "6.1.1 - Blender Speed", "6.1.2 - Hot Cup Density", "6.1.3 - Viscosity", "7.1.1 - ANFO Test", "8.1.1 - Calibration - Unit 8018", "8.1.2 - Calibration - Unit 8025") ' there are a total of 27 potential sheets, however, only the ones with a value of 1, found at range_create.Offset(offset_var, 0), will actually exist While offset_var < 27 If range_create.Offset(offset_var, 0).Value = 1 Then create_array = all_graphs(offset_var + 1) End If offset_var = offset_var + 1 Wend ' move chart sheets to the end of a new workbook Sheets(create_array()).Move The section of my code that doesn't seem to work, at all, is Sheets(create_array()).Move. How do I implement this properly, or is there a problem with the previous section? I originally had it say: If range_create.Offset(0, 0) = 1 Then Sheets(Array("1.1.1 - LD AN Consumption").Move End If ' etc... all the way to the last one, but it only worked for the first one, and then crashed, it didn't want to add it to the same "new" workbook or another new one, which wasn't my needs anyways Thanks you Jon Peltier, Peter T., Dave Peterson, and ND Pard for all your help on that last section of code. I am still stuck working on it, but it is not necessary to complete at this time Tag: workbook / worksheet change event Tag: 981148
  - 13
    - Selfcert no longer working correctly I have used Selfcert successfuly for years. Recently it started asking to 'Enable Macros each time it was opened. I went to VBA Tools -> Digital Signature -> Choose and my signature when I chose it would not stick. Then I removed it and tried to use Selfcert to get it working again. Each time I get he message 'An error occurred during certificate creation. Selfcert was unable to create your certificate.'. It does this every time. I cannot find any reason for it, nor find any possible solution. TIA -- Gus Evans Tag: workbook / worksheet change event Tag: 981146
  - 14
    - Check access right to folder Hi Actually an easy one but I can't find anything on google. How can I check the access right to a folder (I just want to know if the user of the macro has access to a shared folder)? Is there any function that does that? (that would be on a network drive) thanks in advance Charles Tag: workbook / worksheet change event Tag: 981143
  - 15
    - Save Date How can I update the value in a cell that represents the last date the workbook was saved? Tag: workbook / worksheet change event Tag: 981137
  - 16
    - Userform: what happen to the sub who launched it? Hello quick question on userforms. If I have in the following sub something like sub LaunchUserform load userform1 userform1.show end sub what happens to the sub once the userform has been loaded and appeared on the screen? Is the sub terminated? If not at what point does the sub resumes? Same thing when I want to unload the userform, from a sub within the userform. Let's say that in one of the procedures contained in the userform I have: sub DoTheStuff if me.TheValue=1 then unload me end if msgbox "ok" unload me end sub I have the feeling it doesn't always unload the userform after the unload me, and that it doesn't interupt the sub DoTheStuff at unload me (I am working on a much more complicated macro, so it's not as easy to spot what's going on). How can I say both unload the userform and exit the sub at the same time? I don't know if this is related but I am also using a userform close sub which closes a DAO link and cleans up: Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer) Is there a chance that this is interfering with how the userform can be unloaded? The reason I ask is that sometimes when the userform gives the feeling it is done and gone, it looks like VBA is still active and the link to the DB not close (and I can interrupt the code by using the Reset button in VBA). So my ultimate question is something like is there an ultimate/propper/ ultra clean syntax or steps to follow to deal with userforms and loading/unloading them properly? If anyone has some lights on the topic, would be greatly appreciated. Thanks in advance Charles Tag: workbook / worksheet change event Tag: 981127
  - 17
    - Excel VBA Query Across Databases I'm trying to populate a recordset with 2 tables from seperate databases. So far, I get no error when I run the query, but it's also not populating the field from the second database (which happens to be the one I'm actively in). The query is below: With comboPubs ' Assign the Connection object. .ActiveConnection = cnPubs ' Extract the required records. .Open "select strName from db1_name..TblAnalysisOptions AO join db2_name..TblResultHeader RH on AO.guidAnalysisOptions=RH.guidAnalysisOptions" ' Copy the records into cell A1 on Sheet1. Sheet1.Range("A1").CopyFromRecordset comboPubs ' Tidy up .Close End With Is there anyway to do this? Both our on the same machine. Tag: workbook / worksheet change event Tag: 981117
  - 18
    - Copy rows from multiple workbook into a different workbook (sheet) Please I am very new to Macro. Please Help me on this one. I will really appreciate it. Will like to copy multiple rows from different workbook into one sheet in a different workbook. Each row has the same headings. How do i go about about to use macro. Again I haven't used it before. Please pardon my ignorance. Thanks for your help Tag: workbook / worksheet change event Tag: 981113
  - 19
    - Staff resource query Hi, How can I use the following input to populate the "Staff Count" as shown in the example below. Start Finish Person1 06:00 08:00 Person2 07:00 09:30 Time Staff Count 06:00 1 06:10 1 06:20 1 06:30 1 06:40 1 06:50 1 07:00 2 07:10 2 07:20 2 07:30 2 07:40 2 07:50 2 08:00 1 08:10 1 08:20 1 08:30 1 08:40 1 08:50 1 09:00 1 09:10 1 09:20 1 09:30 1 kind regards, Matt Tag: workbook / worksheet change event Tag: 981112
  - 20
    - Follow Hyperlink in Cell I have a 3 column range on a sheet. The first column populates a combobox on a form. The 3rd column is a hyperlink to subfolder on a server. Example: X:/MRASpreads/123Forms/Amendments When I access the sheet itself and click on the cell with the hyperlink, everything works fine and I don't get any warning messages. Explorer just opens up in that subdirectory. I'm trying to get explorer to open up via code. The following works: ActiveWorkbook.FollowHyperlink Address:= _ Application.VLookup(ComboBox3.Value, Range("FullLocn"), 3, False), _ NewWindow:=True The problem with the above is that it displays the virus warning message before opening up explorer. I've read that the warning can't be disabled but since this can be done directly from the worksheet, there must be another (better) way to do this than the way I'm trying to it. Any ideas? Thanks, John Tag: workbook / worksheet change event Tag: 981110
  - 21
    - Comparing 2 sheets I have combined two files into one file. I used copy/paste to enter the contents of file 2 into a new sheet on the original file. I now have a single file which contains 2 sheets. Let's name the sheets: Source (smaller) and Original (larger). Now, I want to use column A containing 419 rows of the Source sheet to search column A containing 4,763 rows of the Original sheet and highlight the ones that matching names. Example: "ABC Industries" is found in column A on both sheets - but in different rows. On the source sheet, it is in row 5. On the original sheet, it is in row 20. I want to use the source sheet "ABC Industries" in row 5 to highlight the "ABC Industries" in row 20 on the original (as it is a match). However, I want automate the process of looking at all 419 names in the source sheet to highlight the matches found in the original sheet. How can I do this? -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.aspx/excel-programming/200805/1 Tag: workbook / worksheet change event Tag: 981107
  - 22
    - Text Box - Initial Content Greetings I am using Excel 2000 I have a spread sheet with columns holding Date, Order No., Name, etc I wish to generate a user form which will display data from a selected row, i.e. the entry's date Order No., Name, etc in Text boxes. This will permit the user to simultaneously review and edit the data in a reduced error manner. I can so far only get a text box to display the existing content of a specific cell, eg. "C15". How do I get it to display the data from appropriate cell of the selected row. So far the Control Source field associated with the Text Box has rejected all my attempts. So far I have defined the appropriate cells as a Range. Tag: workbook / worksheet change event Tag: 981101
  - 23
    - Application.Run Hello, I have been looking at Application.Run I see the following code in MSDN. In this example they use a template name. Does that mean a template can be not opened? And it will run a method from any template? How about an add-on? how about an xla? can you have: Application.Run MacroName:=<<xla>>& "." _ & <<module1>> & "." & <<some method>> ?? strTemplate = InputBox("Enter the template name") strModule = InputBox("Enter the module name") strMacro = InputBox("Enter the macro name") strParameter = InputBox("Enter a parameter value") Application.Run MacroName:=strTemplate & "." _ & strModule & "." & strMacro, _ varg1:=strParameter Tag: workbook / worksheet change event Tag: 981099
  - 24
    - VB/Excel query To assist with office management, I need a VB script or excel macro that does not allow input in a defined column if the data being input is not listed on another column. For ex: the file has a sheet marked Operator Name, which lists names of all the staff members. The other sheet is an input sheet which requires us to input the name of the staff and their performance details. I need a VB script/excel solution that does not allow the inputter to input a name that is not listed on sheet marked Operator Name. (Data validation or List would not be appropriate as the team size is large and the inoutter will have to scroll through a huge list of names). Also, if I am not being too greedy, could it be possible for the script to mention the closest possible name available on the Operator Name sheet in case the inputter mis-spells the name. Thank you for your assistance. Regards, PD. Tag: workbook / worksheet change event Tag: 981098
  - 25
    - Compile Error in Hidden Module: Module 1 I have a workbook that has code that works on my computer, and a couple of other computers. However, when the workbook is emailed to someone, then emailed back, I get the compile error above. I originally had the code password protected, but have since removed the password thinking that could be the problem - but that didn't work. the one thing I did notice was that the computer to which the file was sent has Windows SP3 installed, while the computer getting the error on return has SP2 installed. Could this have anything to do with it? Does anyone know to what "Hidden Module" refers to if the code is not password protected? Thanks Tag: workbook / worksheet change event Tag: 981082

Is it possible to use the act of navigating to a different worksheet as an
event in VBA programming? For instance, if I had a workbook with multiple
worksheets, and the primary sheet that is displayed when the workbook is
opened is called "setup", I want to make sure that you cannot navigate to or
view any other worksheets until all necessary changes are made to the setup
worksheet.

Top

RE: workbook / worksheet change event by GarysStudent

GarysStudent
Fri May 09 10:12:02 CDT 2008

Use the worksheet Change event on the "setup" worksheet to set or clear a
global navagate_allowed flag.

Use the worksheet Activate event on all other worksheets to check the flag
and kick the user back to "setup" if the flag is False.
--
Gary''s Student - gsnu200785

"Jeff" wrote:

> Is it possible to use the act of navigating to a different worksheet as an
> event in VBA programming? For instance, if I had a workbook with multiple
> worksheets, and the primary sheet that is displayed when the workbook is
> opened is called "setup", I want to make sure that you cannot navigate to or
> view any other worksheets until all necessary changes are made to the setup
> worksheet.

Top