Posts by Arce2ee

    Re: VBA editor opens when macro initiated


    My security is set at low and most everyone else in our office is at medium. It's not that it won't run the macro, it's just that when I first initialize the macro instead of just doing it's thing, it opens the editor. If I type the password in and hit the play button it works fine.


    At first I thought I must have changed something by accident, but then I tried one of our Word documents that has macros and it did the same thing, so I concluded that it must be Office. But not even all of Office, because macros in Outlook work just fine, so I was back to Word and Excel being screwed up.


    As a last resort I have re-installed Office and everything seems to back to normal, but I'd rather know what happened so as to avoid doing it again, or at least know how to fix it withour re-installing software.


    Thanks for the reply.

    I have a workbook with a small macro that is protected by a password. When I run the macro the VBA editor starts and the macro stops. When I unprotect the macro and hit the run button, it will start and run the macro just fine. The same workbook works "flawlessly" on other workstations, so I am thinking that it has to be a setting in Excel that I must have changed, but for the life of me I can't find a setting that will stop this from happening.


    I am hoping that someone who reads this will know of a solution.


    Thanks for any and all help!!


    **Just discovered that this is not limited to Excel but does the same thing to WORD documents with macros as well.

    I want to copy multiple sheets from one workbook to another. The sheets are linked together though so I have to copy them as a group rather than one by one. I have the following code and I think I just need to declare a variable somewhere but I'm not sure what or how. Any help would be great.


    Sub PanelImport()
    current = ActiveWorkbook.Name
    Msg = "You are about to copy sheets from another job. Do you want to continue?"
    Style = vbYesNo + vbCritical + vbDefaultButton2
    title = "Panel Copying"
    Response = MsgBox(Msg, Style, title)
    If Response = vbYes Then
    data = Application.GetOpenFilename("Excel files (*.xls), *.xls", , "Please select the Excel file that you want to copy from.")
    If data = False Then
    Exit Sub
    End If
    Else
    Exit Sub
    End If
    sheetadd = Empty
    Workbooks.Open(Filename:=data).RunAutoMacros Which:=xlAutoOpen
    wbo = ActiveWorkbook.Name
    For Each wsSheet In Worksheets
    wsSheet.Visible = True
    If wsSheet.Cells(1, 1) = "D" Or wsSheet.Cells(1, 1) = "M" Or wsSheet.Cells(1, 1) = "M1" Or wsSheet.Cells(6, 10) = 15 Or wsSheet.Cells(38, 10) = 1 Then
    If sheetadd = Empty Then
    sheetadd = "Sheet" & wsSheet.Index
    Else
    sheetadd = sheetadd & """, """ & "Sheet" & wsSheet.Index
    Sheets(Array(sheetadd)).Select
    End If
    Else
    wsSheet.Visible = False
    End If
    Next wsSheet
    ActiveWorkbook.Close SaveChanges:=False
    End Sub

    I am doing a calculation in a userform and pulling the information from charts in Excel. I want to be able to scroll in the Excel sheet while still having the userform open and the macro is waiting for the next control button to be clicked.


    Is there a way to shift the focus back to the excel sheet and then back to the userform?

    I am trying to sort text in VBA but am having problems getting the information in order. The information I am trying to sort is:


    6,9,4,10,1,11


    but I get: 1,10,11,4,6,9


    Is there a way to get this to sort the text as 1,4,6,9,10,11?


    I cannot change the format to numbers it must remain text.


    Any help would be appreciated.

    It might be a little late, but I had the same problem until today thanks to Ozgrid user FWind. What we discovered in my office is that if you cut the cells out of Excel and paste then into Notepad and then save as a txt file the double quotes don't show up. Here it the code we are using. I read this before and thought maybe this would help you.


    Sub exportdata()
    Cells.Select
    Selection.Copy
    Shell "notepad.exe", vbNormalFocus
    SendKeys "^V"
    SendKeys "%fa"
    End Sub

    Does anyone know how to change the default workbook that Excel opens when you start a new workbook? I need to have the cells in the "text" format not the general, and I cannot change the format of the cells after it opens. I am trying to import a txt file into Ecel with fractions and Excel keeps converting it to a date.:mad: I have tried setting up a template, but so far I can only get this to work on start up and not when I open a subsequent workbook(s), also a must. Any help that you might have would be appreciated.


    Thanks

    I don't touch the validation in my code at all. i first thought it was me turning the screen updating on and off, but I dismissed that after a couple of tests. I was on 2000xl and was just updated to XP a few days ago, but the problem persists in both versions. I work on it at home on a Mac version and the same problem is there.

    I have an ever changing workbook that started with data validation in the individual worksheets, and since I have added macros to it. The error and warning messages still appear, but the pull down arrow no longer appears to select from the lists. Anyone have an idea why?

    I am trying to run a macro contained in workbook "A" through another macro in workbook "B". However, the macro in "A" needs a variable from workbook "B". I haven't had any success passing the variable. The following is the code I use to initialize the remote macro:


    file = "h:\Panel - Template.xls"
    Workbooks.Open Filename:=file
    Application.Run "'Panel - Template.xls'!subName"


    Any help would be great.

    I am sure this is an easy question for somebody out there but I can’t seem to figure it out for the life of me. I have (3) worksheets. A, B and C. On sheet A, an end user will hit a button and enter data into a userform. I am having problems getting the information to sheets B and C without those sheets being the active sheet. Right now I am using


    Sheets(“B”).select

    So the information gets routed to the right sheet, and this works, but I have to jump back and forth between sheets to often in the code and the user sees a lot of screen jumping that I would like to get rid of.


    Any suggestions would be appreciated.

    I haven't had time to try your idea, till recently. Thanks for the help and suggestions. I was able to use 'xlNext' with my code and added a couple of logic statements and it works great.


    Thanks again for your help.