Posts by socha

  • FileSearch Fails In 2007

    Before upgrade to Microsoft 2007 this code worked well (for 3 years). I marked it well with big space & comment where it gets hung up. The line says: With Application.Filesearch. All is well up to that point. I have another program that seems to have a problem when it comes to "With Application" as well. It must have to do with the upgrade, because my programs have been used daily, and it was right after the upgrade that it got buggy. All of the users were upgraded to Microsoft 2007 at the same time.



  • Saving in 2007 Loses Functionality In Lower Versions

    My team works with several Excel documents that have VBA code, and we share these documents among us. One of our team has the 2007 version of Excel-- the rest of us don't. Whenver she saves one of them, it loses all of it's VBA functionality for the rest of us. Then basically I have to delete the whole record, and recreate it, and repopulate the new one with the relevant data. It's not working out.


    When she opens them there is a message that says to the effect of: "my form of Windows is higher than yours, there might be some things lost when it is reopened in a lower form."


    Is there a way to work around this problem so that we can all share the records without ruining them? the obvious solution would be to upgrade all of us to 2007, however that may not be an option for us. The other obvious solution is to give her a lower version. I'd rather not do that to her if there is some other way.

  • Cell Properties Not Moving With Cells.

    This was a frustrating turn of events. I have workbooks that have a lot of Macros, and it has been working very well. Up until now. There are two of my users that had Permissions, that is, they could edit certain ranges without a password, where anyone else would have had to enter a password.


    I went through those workbooks, added some rows, deleted some rows, because some updates had to be made. With most features that are connected to certain ranges, whatever the feature is moves if that range moves (for instance formatting, range names, linked controls, etc). Turns out my permissions did not move along with the ranges. Now I will have to loop through the workbooks with a macro, delete the permissions and add them again with the correct ranges. This is survivable, but my problem is that I am sure I will have to make updates in the future, and what a pain to have to make those corrections every time! Can anyone tell me how I can set up the permissions so that they will move along with the ranges?



    Most appreciative, thanks!

  • Drop Down Form Field

    Hi, I'm having some trouble with a form field in Word. I have a drop down field and I don't see any way to resize it. It will not allow my to do it by dragging the end, and the properties window doesn't have anything about size. The help menu is not a help-- it only talks about resizing dropdown fields in toolbars. This is by itself, not a toolbar. I need to make it larger so that all the text can be seen. Can anyone help?

  • Group Like Data Together

    I would like to be able to contanate data for each person. The ID# (first column below) identifies the person, and I wish to use a new column to concatenate.


    For example, Susie Smith's new data in the 5th row should read "France, Mexico". I know how to concatenate, but only if it's simply by cell references. But how do I reference each of her ID #s in the CONCATENATE formula, so that it knows which rows are Susie, and therefore to be included in the concatenation?


    I've tried messing with arrays which is so hard for me to get. I don't know if this would be done with array, or with lookup formulas, or both. Can someone help me?


    124123 Susie Smith France
    124123 Susie Smith Mexico
    324442 James Winters China
    324442 James Winters China
    324442 James Winters Canada
    343421 June Daly Finland
    232323 Bobby Jones Mexico
    232323 Bobby Jones Spain

  • Embedding Shockwave into Powerpoint presentation

    We are trying to embed (not link) a flash video into a PowerPoint presentation. Technically this is not compatible, but we think there is a way to work around it. We have linked it and it only works on one computer. But we need to be able to access it from CD rom in computers not connected to a network.
    When trying to access it through the CD rom an error message says "PowerPoint couldn't locate or start P:/path/xxx.swf".


    We tried copying the movie to the CD rom, but it still seems to be trying to pull the original file. How can we resolve this?

  • Error when initializing UserForm

    Re: Error when initializing UserForm


    The lstxxx are Listboxes. When I put .Value=, I get the message:
    Could not set the Value property. Invalid property value.


    But only on the second listbox-- the one with the date format.
    It's the exact same thing when I use .Text= ... It works for all except the formatted one.


    If I use .List= or .ListIndex= it doesn't like it at all for any of them.

  • Error when initializing UserForm

    I am populating a UserForm from some ranges on my workbook. All is well EXCEPT for the third guy down- "lstCheckDep". The only difference I can figure out is that the cell it is drawing from has a date format. How can I adjust for this so that it's contents can go on the UserForm, without getting hung up on the formatting?


    Code
    Private Sub UserForm_Initialize()


lblCandidate.Caption = Range("Rcandnam").Value & " - #" & Range("ID").Value
lstCheckCntry = Range("RCountry").Value
lstCheckDep = Range("RDeparture").Value
lstCheckTrack = Range("rTrack").Value
lstCheckLang = Range("Lang").Value
End Sub

  • Unprotect worksheet event?

    Re: Unprotect worksheet event?


    Jmhans: I am already using the Control Toolbox for these controls; haven't used the Forms controls for this at all. I don't understand how putting Locked to "False" will keep the controls protected , but I will try it.


    Dave: I do have the controls locked, and the linked cells locked as well. I am going to try Jmhans' suggestion of unlocking the controls.


    Andy: Wow, this is some fancy footwork. I don't think I understand it all, but if I am still stuck after I try unlocking the controls I will take on the challenge of understanding and applying that. It looks like a process I can learn a lot from.


    Thanks all.

  • Unprotect worksheet event?

    There really isn't an "Unprotect" worksheet event. I am hoping to find a way to automate something when I unprotect and protect a sheet.
    I have checkboxes, option buttons & text boxes that I want to protect when the sheet is protected. It's odd, but even though those controls are linked to locked cells, it allows me to select them and change them (even while I get the message that the worksheet is protected).


    So I want to enable them when the sheet is unprotected, and disable them when the sheet is protected. I know the code for enabling and disabling them-- I just don't know where to put that code, to make it automatically run when the sheet is protected or unprotected.

  • Option Button Properties change based on a Checkbox

    I would like to set up some option buttons so that the will be become visible and enabled when a check box is checked, and the opposite when the same checkbox is not check. This is my problem code:



    It is the 5th command where it gets hung up:

    Code
    For Each myOption In pDiscounts.Controls


    I am sure I am spelling both of the names correctly: the checkbox, and the group name.

  • Can a macro cancel changes since the last Save?

    What I'd like to have is a message box with 3 buttons:


    Yes
    No
    Cancel


    Yes- saves the data that was just entered, and then continues the macro.
    No- saves the data just entered, but exits the macro.
    Cancel- exits the macro, but DELETES THE CHANGES since the last save.


    I don't of a way to ask Excel to delete the changes since the last save without closing the book. Maybe I could get around it by having the Cancel button close the book without saving it and then re-opening it, but I don't think I could have it recognize the particular book once it is closed.

  • Range Names quirky when a new sheet is copied onto workbook

    I've got several workbooks, each with 2 sheets: "Main Record" & "Documents". both sheets have a lot of range names. I had to make a lot of changes to the format of the Documents sheet, and add a new sheet called "Kids". I ran a Macro that looped through the workbooks, replaced the Documents sheet with the new one (named ranges & all), and then copied the new "Kids" sheet onto the workbooks too (with named ranges also). It seems that the 2 new sheets don't recognize the named ranges from the unchanged "Main Record" sheet, and visa versa. Where did I go wrong, and more importantly what can I do to fix this?

  • Importing UserForms via VBA

    Re: Importing UserForms via VBA


    The reason why I didn't do Save As is because there were several sheets and modules that I didn't want in the new one. I solved the problem by setting up a variable for the name of the new workbook...


    Code
    Dim newBook As Workbook
Set newBook = Workbooks.Add
'Copy worksheets to a new record
With ThisWorkbook
 .Worksheets("Template").Visible = True
 .Worksheets("Template2").Visible = True
 .Worksheets("Template3").Visible = True
 .Sheets(Array("Template", "Template2", "Template3")).Copy Before:=newBook.Sheets(1)
End With


    Then I used that variable, newBook, when I imported the forms & modules.
    This has now been solved. (hear the cheers from my office!)
    Thanks for the help.

  • Importing UserForms via VBA

    Re: Importing UserForms via VBA


    I see part of the problem. I am using a workbook as a template, copying that, and then importing user forms and some modules to the new workbook (because they don't automatically copy over).


    I tried skipping over the userform import commands to see what happened, and, and the code following them started acting on the template workbook instead of the new one. ...and yes, the template did have all my userforms already in it. So that's why it wouldn't import them,... as you suggested they are already there.


    So now I go to work to try and figure out why it's working on my template instead of the new book. So curious, because I didn't change anything... but now I know where to be looking in order to solve my new problem.

  • Importing UserForms via VBA

    Re: Importing UserForms via VBA


    Wow, thanks for that quick reply.


    The error message says:
    Run-time error ‘60061’:
    Errors during load. Refer to ‘\\DOMAIN2\Private\UTP\Candidate Tracking\VBComponents\UserForm4.log’ for details


    When I go to that log it says:
    Line 2: The Form or MDIForm name UserForm4 is already in use; cannot load this form.


    However, when I look at the forms modules in the document I am importing it to, that one does not exist. Strange thing… after some monkeying around with the pathname for that first command, it is letting me load user form 3, even though I ended up with exactly the same code that I had before. I don’t know why it’s letting me load user form 3 now and not user form 4, when everything seems identical.

  • Importing UserForms via VBA

    Code
    Application.VBE.ActiveVBProject.VBComponents.Import ("\\DOMAIN2\Private\UTP\Candidate Tracking\VBComponents\UserForm3.frm")


            Application.VBE.ActiveVBProject.VBComponents.Import ("\\DOMAIN2\Private\UTP\Candidate Tracking\VBComponents\UserForm4.frm")


    These commands for importing user forms used to work fine. The path really does have those forms in it, however IS switched me to Terminal Server 2003. My security is set to "Trust access to Visual Basic Project", so that is not the problem in this case. The error message tells me to go to the log to see the problem, and it says the form is in use so it can't be imported. However I don't have the form open, and the import used to work with this exact code.


    Perhaps my library references got re-set when they changed me to Terminal Server 2003. These are the ones that are checked off:
    Visual Basic for Applications
    Microsoft Excel 11.0 Object Library
    OLE Automation
    Microsoft Office 11.0 Object Library
    Microsoft Forms 2.0 Object Library
    Microsoft Outlook 11.0 Object Library
    Microsoft Office Outlook View Control
    Microsoft Visual Basic for Applications Extensibility 5.3
    Microsoft Word 11.0 Object Library
    Microsoft DAO 3.6 Object Library
    Microsoft ActiveX Data Objects 2.0 Library