WM_SETREDRAW doesn't work unless...what?

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
    - 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: WM_SETREDRAW doesn't work unless...what? Tag: 981226
  - 2
    - 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: WM_SETREDRAW doesn't work unless...what? Tag: 981221
  - 3
    - 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: WM_SETREDRAW doesn't work unless...what? Tag: 981219
  - 4
    - 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: WM_SETREDRAW doesn't work unless...what? Tag: 981217
  - 5
    - 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: WM_SETREDRAW doesn't work unless...what? Tag: 981209
  - 6
    - 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: WM_SETREDRAW doesn't work unless...what? Tag: 981199
  - 7
    - 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: WM_SETREDRAW doesn't work unless...what? Tag: 981193
  - 8
    - 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: WM_SETREDRAW doesn't work unless...what? Tag: 981192
  - 9
    - 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: WM_SETREDRAW doesn't work unless...what? Tag: 981190
  - 10
    - 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: WM_SETREDRAW doesn't work unless...what? Tag: 981188
  - 11
    - 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: WM_SETREDRAW doesn't work unless...what? Tag: 981185
  - 12
    - 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: WM_SETREDRAW doesn't work unless...what? Tag: 981182
  - 13
    - 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: WM_SETREDRAW doesn't work unless...what? Tag: 981179
  - 14
    - 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: WM_SETREDRAW doesn't work unless...what? Tag: 981175
  - 15
    - 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: WM_SETREDRAW doesn't work unless...what? Tag: 981174
  - 16
    - 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: WM_SETREDRAW doesn't work unless...what? Tag: 981162
  - 17
    - 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: WM_SETREDRAW doesn't work unless...what? Tag: 981154
  - 18
    - 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: WM_SETREDRAW doesn't work unless...what? Tag: 981153
  - 19
    - 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: WM_SETREDRAW doesn't work unless...what? Tag: 981148
  - 20
    - 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: WM_SETREDRAW doesn't work unless...what? Tag: 981146
  - 21
    - 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: WM_SETREDRAW doesn't work unless...what? Tag: 981143
  - 22
    - Save Date How can I update the value in a cell that represents the last date the workbook was saved? Tag: WM_SETREDRAW doesn't work unless...what? Tag: 981137
  - 23
    - 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: WM_SETREDRAW doesn't work unless...what? Tag: 981127
  - 24
    - 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: WM_SETREDRAW doesn't work unless...what? Tag: 981117
  - 25
    - 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: WM_SETREDRAW doesn't work unless...what? Tag: 981113
- Next
  - 1
    - 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: WM_SETREDRAW doesn't work unless...what? Tag: 981112
  - 2
    - 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: WM_SETREDRAW doesn't work unless...what? Tag: 981110
  - 3
    - 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: WM_SETREDRAW doesn't work unless...what? Tag: 981107
  - 4
    - 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: WM_SETREDRAW doesn't work unless...what? Tag: 981101
  - 5
    - 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: WM_SETREDRAW doesn't work unless...what? Tag: 981099
  - 6
    - 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: WM_SETREDRAW doesn't work unless...what? Tag: 981098
  - 7
    - 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: WM_SETREDRAW doesn't work unless...what? Tag: 981082
  - 8
    - Variable length named ranges I need a way to specify a variable for the last row in a named range. A static definition looks like this: ThisWorkbook.Names.Add Name:="dAge", RefersTo:="=RawData!$H$2:$H$100", Visible:=True I would like to replace â??100â?? with â??nRowsâ?? and need help with the syntax. Thank you. Tag: WM_SETREDRAW doesn't work unless...what? Tag: 981077
  - 9
    - ms excel 2007 Filesearch I have the following code developed in Excel 2002. I have noted from this group that .Filesearch is not longer supported in ms excel 2007. I would like for this code to search for text "budget" in all workbooks and return the file and it's path to either a listbox or compile a list of all of the files names and path into an Excel Workbook. I am kind of lost on reworking this code with VBA in MS Excel 2007. Any suggestions you have would be greatly appreciated Sub findfiles() With Application.FileSearch .NewSearch .LookIn = "C:\" .SearchSubFolders = True .TextOrProperty = "Budget" .MatchTextExactly = False .Filename = "*.xls" .Execute For i = 1 To .FoundFiles.Count UserForm1.ListBox1.AddItem.FoundFiles (i) Next i End With UserForm1.Show End Sub Tag: WM_SETREDRAW doesn't work unless...what? Tag: 981070
  - 10
    - Math Operation: Set Subtraction I have two range objects, and I want to subtract range B from range A. That is, I want the opposite of Application.Intersect. This match concept is illustrated at http://www.mathwords.com/s/set_subtraction.htm I don't believe there's a single command for this, but can anyone provide some thoughts? I plan on approaching this using a loop, Application.Intersect, and Union() commands. Thanks, Matthew Pfluger Tag: WM_SETREDRAW doesn't work unless...what? Tag: 981064
  - 11
    - Select Column based on Sheet Name I have a macro that will copy and add a sheet, and name it for each name in the first row. I now need to select the column in each sheet where the sheet name and header name are the same and set the background to gray. The number of columns and rows will vary. Is this possible to do? Thanks for all your help. I use this site a lot. Tag: WM_SETREDRAW doesn't work unless...what? Tag: 981061
  - 12
    - Help with Grabbing Cell Value from Range This is the code I have: ' Grab the database name Dim strDb As Range Set strDb = Worksheets("Dashboard").Range("C2") Dim strDBString As String Set strDBString = strDb.Text The range is assigned without a problem; however, extracting the actual text value seems never to work. Can anyone clarify exactly how this should happen? My underlying motivation is to pull a cell value in VBA, and then use that in a SQL query within VBA as well. Tag: WM_SETREDRAW doesn't work unless...what? Tag: 981055
  - 13
    - Sheet Protection Question Greetings, I have a worksheet with linking formulas in 79 cells (named ranges), Toolbars>Control Toolbox>CommandButtons and a Toolbars>Forms>ComboBox. This worksheet is set up as a form to view individual records chosen from the ComboBox. This form is setup to do one of two tasks. First view the record. And second, edit the record. To view: I need to only see the information in the record and not be able to change anything - until I click the "Start Edit" button. To edit: All of the formulas are changed into values. Then I can edit any or all of the cells.When finished, I click the "End Edit" button, which saves the edited version of the record into the "Customer List" sheet and then puts the formulas back into the cells. The only thing I am missing is how to keep from overwriting the formulas (in the view mode) with vba. The problem I ran into with Protect was that the CommandButtons were also protected and I need them to always be unprotected. Is there a way to lock everything but the CommandButtons and the ComboBox? I'm not even sure if protection is what I need. Security is not an issue here. Overwriting the formulas is. Another possibility would be if when the user entered any of the cells on that page, to automatically trigger the edit mode. The cells all have names (pfCell_4 thru 79 - eg. pfCell_4, pfCell_5, ... pfCell_79). Any help will be greatly appreciated. -Minitman Tag: WM_SETREDRAW doesn't work unless...what? Tag: 981054
  - 14
    - Tracing where spreadsheet is opened from Dear All I am currently opening an existing Excel file from a macro within SolidWorks (thus written in VBA). Is there any way that when the spreadsheet opens, it checks to see where it was opened from? If it is opened from SolidWorks I would like it to launch a certain macro within the spreadsheet. Many thanks in advance Andy Tag: WM_SETREDRAW doesn't work unless...what? Tag: 981053
  - 15
    - Retrieving the values of Excel check boxes in .NET I have an Excel spreadsheet which contains multiple un-grouped check boxes (from the Forms toolbar) which are not linked to any particular cell(s). I want to read their values in .NET code. For this I'm using 'Microsoft Office Interop Excel' and 'Microsoft Vbe Interop Forms' libraries. This is the code I'm using: String excelFile = "C:\\FileName.xls"; Excel.Application estimate = new Excel.Application(); Excel.Worksheet workSheet = new Excel.Worksheet(); estimate.Workbooks.Open(excelFile, 0, true, 5, "", "", false, Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false); workSheet = (Excel.Worksheet)estimate.Worksheets.get_Item("WorksheetName"); Excel.CheckBox cb = (Excel.CheckBox)workSheet.Shapes.Item("CheckBoxName").OLEFormat.Object; cb.Value returns the value of the check box (the value is -4146 if the check box is unchecked, 1 if it is checked and null if it is mixed). At some point in time I was able to see the names of all the check boxes in my worksheet (they were named "Check Box 1", "Check Box 2" and so on). But now I see that all the check boxes have the same name "CheckBox" which is very strange. This is making it impossible for me to retrieve the values of the check boxes by specifying the check box name. I know I can use the index also but it poses a similar problem - 'how do I know the index of each check box?'. Coming back to the name issue, is there any way I can see the unique names of all my check boxes again? Is there any mode which I have to use to be able to see the unique names? What am I missing here? Any help would be greatly appreciated. Thanks in advance. Tag: WM_SETREDRAW doesn't work unless...what? Tag: 981050
  - 16
    - Userform Initialize Problem ! Dear Sir, I have a Userform for the data entry purpose, when I press the OK button then records are written on the sheet, and one message is appearing YES/NO. if I press Yes, then Userform must be clear all fields. but at the same time message appearing "Please use numbers only" infact I put the restriction on the Unit Price field, that should be numbers only. then I dont understand why this message is coming when I press OK button. any suggession ??? I AM USING THE BELOW CODE ========================= Private Sub CommandButton3_Click() ' for ok button ActiveWorkbook.Sheets("DailyPurchase").Activate Range("A5").Select ' to fill up combo box for the category If ComboCategory.ListIndex = -1 Then MsgBox "You must choose the category SHAHZAD" ComboCategory.SetFocus Exit Sub End If ' to fill up Date If DatePurchase = "" Then MsgBox "you must provide date" DatePurchase.SetFocus Exit Sub End If ' to fill up QTY If txtQuantity = "" Then MsgBox " you must provide Quantity " txtQuantity.SetFocus Exit Sub End If ' to fill up Unit price If txtUnitPrice = "" Then MsgBox " you must provide Unit Price " txtUnitPrice.SetFocus Exit Sub End If Do If IsEmpty(ActiveCell) = False Then ActiveCell.Offset(1, 0).Select End If Loop Until IsEmpty(ActiveCell) = True ActiveCell.Value = DatePurchase.Value ActiveCell.Offset(0, 1) = TxtDescription.Value ActiveCell.Offset(0, 2) = txtQuantity.Value ActiveCell.Offset(0, 3) = txtUnitPrice.Value ActiveCell.Offset(0, 5) = ComboCategory.Value ActiveCell.Offset(0, 6) = txtPR.Value ActiveCell.Offset(0, 7) = txtRemarks.Value ' Range("A5").Select ' Ask and Do Procedure If MsgBox("One record is written, do you have more entries ?", vbYesNo, "Title") = vbYes Then Call UserForm_Initialize Else Unload Me End If End Sub Private Sub UserForm_Initialize() TxtDescription.Value = "" DatePurchase.Value = "" txtUnitPrice.Value = "" txtQuantity.Value = "" txtPR.Value = "" ComboCategory.Value = "" txtQuantity.Value = "" txtRemarks.Value = "" DatePurchase.SetFocus End Sub Private Sub txtUnitPrice_change() If Not IsNumeric(txtUnitPrice.Text) Then MsgBox "Please use numbers only" Exit Sub End If End Sub =================================== Please give me the solution, I dont need this message when I press OK button. Regards. Shahzad Tag: WM_SETREDRAW doesn't work unless...what? Tag: 981049
  - 17
    - worksheet SaveAs WK4 in background I have an excel workbook with multiple sheets. I want to save one of the sheets as a WK4 in the background. I amusing the following code: Sheets("sheetx").SaveAs Filename:= "C:\xxx.wk4", FileFormat:=xlWK4, CreateBackup:=False The problem is that although the excel appears to still be open with all of the tabs, it has been renamed at the top to the WK4 filename. If I close the excel and open the WK4 then it opens as WK4 with only the sheet that I saved. My problem is that users may have an issue with the excel being "lost" while the supposed WK4 file looks like excel. SaveCopyAs does not seem to help because that cannot be done against a single sheet and cannot convert to WK4. Any ideas about how I could do a SaveAs only in the background without renaming the excel? Tag: WM_SETREDRAW doesn't work unless...what? Tag: 981047
  - 18
    - Please help! I want to set up a condition where a row is deleted ONLY if BOTH cells equal zero. Currently the macro below delete the row when the first cell = zero. Thus when there is something in the second cell and the first cell is zero the row is deleted. The code is below if someone could assist I would be greatful. ' Sheets("Report").Select Cells.Select Selection.Copy Sheets.Add Cells.Select ActiveSheet.Paste Range("D11:H120").Select Application.CutCopyMode = False Selection.Copy Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Dim DeleteFlag As Boolean DeleteFlag = True While DeleteFlag = True DeleteFlag = False Range("A11:H120").Select For Each rw In Selection.Rows If rw.Cells(1, 4).Value = 0 And rw.Cells(1, 9) = 0 And IsNumeric(rw.Cells(1, 1).Value) And Not IsEmpty(rw.Cells(1, 1)) Then rw.Delete DeleteFlag = True 'rw.Cells(1, 10).Value = "Zero" End If Next rw Wend Application.CutCopyMode = False Range("D16").Select ActiveWindow.Zoom = 85 Range("A3").Select Selection.Copy Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False ActiveSheet.Name = Right(Trim(Selection.Value), 5) Application.CutCopyMode = False Range("A3").Select Sheets("Macro1").Select Range("C2").Select Selection.Delete Shift:=xlUp Sheets("Report").Select Range("A3").Select ActiveCell.FormulaR1C1 = "=Macro1!R[-1]C[2]" Range("A3").Select Sheets("Data").Select Range("D4:E88").Select Selection.ClearContents End Sub Tag: WM_SETREDRAW doesn't work unless...what? Tag: 981043
  - 19
    - Setting up Sum ranges inside a macro. When I try to set up a sum function in macro, the vertical range of the sum becomes an absolute range that does not expand when I try to add rows between the first and last row of the range set in the macro. The range does not expand. It seems to be anchored at the top of the range and only extends the number of cells down that was set in the macro. How do I set up a sum range that allows new rows to be added inside the range and make the range expand to include the new row of cells? Tag: WM_SETREDRAW doesn't work unless...what? Tag: 981040
  - 20
    - Checkboxes with the same Group Name not Responding I have 4 Checkboxes in a UserForm. I have given all 4 Checkboxes the GroupName = "Shipping" in the Properties Window in the VBA Editor. We I Show the UserForm and click on one Checkbox the others do not turn off, why? Thanks in Advance, Ryan Tag: WM_SETREDRAW doesn't work unless...what? Tag: 981033
  - 21
    - Identify shape in cell I have a sheet that was populated with data from a table copied from a website. One of the rows has cells which contain shapes rather than text. I need to convert these shapes to text and delete the shapes. Two questions: How can I take the current selection and determine just what shape is in it? How can I delete that shape after I set the value property to the desired text? Something like TypeName(Selection) will tell me that I have a shape if what is selected is the shape itself. I actually want to select the cell (or a range of cells) and, based on this, figure out what the shape might be. Thanks! -Martin Tag: WM_SETREDRAW doesn't work unless...what? Tag: 981029
  - 22
    - Iterate rows of spreadsheet(s}\Get values I have two spreadsheets. The first one has a column with numbers in it. The second one has a column with the same numbers and a description column as well. Each number can have more than one description. What I want to do do is iterate the rows in Sheet 1 and lookup the numbers in sheet 2. Then return all the descriptions to sheet 1 and put them in the cell next to the number. If there are multiple descriptions for a number we would like them separated by a comma. Is there a function for something like this? TIA Tag: WM_SETREDRAW doesn't work unless...what? Tag: 981027
  - 23
    - Help with match function, i need to tally Ok, I have thousands of rows of data. One of the columns is titled "age" and each row has a different age. Ranging from 15-100. I need to tally these rows into age group, 0-18, 19-30, 30-50 etc etc. Is there a code i can run that will tally these for me? Thanks, -Adam Tag: WM_SETREDRAW doesn't work unless...what? Tag: 981020
  - 24
    - ADO help: Only get first 255 characters of field... Hi, can somebody help with this? I have an Access database, that has a table with a memo field in it. This table contains records longer than 255 characters. I am using ADO to get this info out of the database into a recordset, and then loop through the recordset to populate a spreadsheet. However, I only get the first 255 characters for the data that comes from the memo field. How do I get the entire value stored in the database? Somebody mentioned using "adLongVarWChar". But how do I do this? Please help, Thanks, Tag: WM_SETREDRAW doesn't work unless...what? Tag: 981019
  - 25
    - distributing a spreadsheet with macro buttons How do you distribute an Excel 2003 spreadsheet application with buttons linked to macros? Do you actually have to relink all buttons to the macros for every new location? I can't believe that - I must be overlooking something. My spreadsheet has buttons which are linked to macros. When the spreadsheet is moved to a different location (on the same computer or a new computer), the following error occurs when a button is clicked: "A document with the name fubar.xls is already open. You cannot open two documents with the same name etc." I moved all macros into the Sheet1 Excel Object. That didn't help. I have tried using buttons from the control toolbox (which I read should correct this but doesn't), and from the forms toolbox, and the "custom menu item" from the macros list. Nothing works. When I put the spreadsheet on another machine, sometimes the buttons are there but I have to link them to the macros. Sometimes the buttons aren't there at all. I don't understand this and can't find anything to clarify it. Where are the toolbars stored? Why can't the buttons just look in the current workbook for the macros? Tag: WM_SETREDRAW doesn't work unless...what? Tag: 981004

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.

***

Top

Re: WM_SETREDRAW doesn't work unless...what? by RB

RB
Fri May 16 18:03:15 CDT 2008

Try a DoEvents in a few places.
Will need a bit of experimenting.

RBS

<baobob@my-deja.com> wrote in message
news:6a3e2e69-fcb1-488a-bf3b-dfcc4c6f5447@m45g2000hsb.googlegroups.com...
> 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.
>
> ***

Top