Excel button problem
in [Programming]
|
Prev: Save
Next: GetOpenFileName
From: BeSmart on 13 Mar 2010 01:31 Hi All I have a macro that copies a worksheet in the active workbook and puts it into a new workbook - then formats it and deletes any buttons on the worksheet. On the first click on the button the macro works ok. On the second click, it fails because the all assigned macros on all buttons in the active workbook changed from "mba" to "book1!mba". Book 1 doesn't exists (wasn't opened, wasn't saved, doesn't have the macros). I've never experienced this problem before?? Can anyone help to solve this problem? FYI The macro to do this is called "mba". It lives in the active workbook within "Module 1" as a "General" macro The active workbook is currently called "Template.xls" - users save as from this workbook The macro is assigned to a button created from the Form toolbar (it's named "creatembabutton") -- Thank for your help BeSmart
From: joel on 13 Mar 2010 04:30 I think you know what the problem is already. Excel doesn't delete the VBA project when a workbook is closed so the project is still active when you run the macro a 2nd time. You always want to run the macro from the first workbook that was opened. You should always refere to the main workbook using THISWORKBOOK. Try to Avoid using Activeworkbook. When you open a workbook use Set bk = workbooks.open(filename:="c:\temp\book1.xls") Then use bk to reference the new workbook. for some reason if you open a text file as a workbook the statement above doesn't work. So I immediately after opening the workbook use a set statement to make a variable equal to the active workbook Set bk = activeworkbook A common problem with VBA is that the focus shifts from one object to another without you knowing it is happening. for example when you open a workbook the focus changes to the workbook that you opened to one of the sheet of the workbook (which ever sheet was the active sheet when the workbook was closed). Without seeing your code I can't tell how to fix your problem. but is is good practive to avoid usiong the following methods: 1) ActiveSheet 2) ActiveCell 3) select 4) Selection With VBA it is inconsistenet and some commands only work with the above methods so that is why I say Avoid. -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this thread: http://www.thecodecage.com/forumz/showthread.php?t=187201 http://www.thecodecage.com/forumz/chat.php
From: BeSmart on 13 Mar 2010 06:10 Hi Joel Please forgive me (I'm a novice at this), and I don't totally understand e.g. how do I avoid using "activeworkbook" when I don't know what the filename of the workbook will be (users save as from the original workbook I send to them)? Here is my code that is assigned to the button. The code gets data from the original workbook and the current worksheet (which is already open and selected because the button is on that sheet) It pastes the data into a section on a hidden worksheet within the same workbook. Then it makes a copy of the MBA worksheet and renames it then the new worksheet is moves out of the original workbook and the formats e.g. clear un-necessary defined names, paste all formulas as values etc, re-hides the There are situation where I use the following - what should I use instead? 1) ActiveSheet 2) ActiveCell 3) select 4) Selection _____________________ Sub mba() Dim nme As Name Application.ScreenUpdating = False Application.EnableEvents = False Application.Goto Reference:="MBAData" 'named range on current worksheet' Selection.Copy Sheets("MBA").Visible = True 'hidden template worksheet' Sheets("MBA").Select Range("A80").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Sheets("MBA").Copy After:=Sheets(Sheets.Count) Sheets("MBA (2)").Name = "Client MBA" Range("A1").Select Application.CutCopyMode = False Range("A1").Select 'from here on it is formatting' Selection.Clear Cells.Select Selection.Copy 'replace all formulas with values Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Range("A1").Select Application.Goto Reference:="MBApaste" Selection.Clear 'clear data dump section Sheets("MBA").Select 'clear the pasted data on the template & re-hide the sheet Application.Goto Reference:="MBApaste" Selection.Clear Sheets("MBA").Visible = False Application.CutCopyMode = False Sheets("Client MBA").Move Range("A1").Select For Each nme In ActiveWorkbook.Names 'delete named ranges not needed in new workbook If nme.Name Like "*_FilterDatabase" Or _ nme.Name Like "*Print_Area" Or _ nme.Name Like "*Print_Titles" Or _ nme.Name Like "*wvu.*" Or _ nme.Name Like "*wrn.*" Or _ nme.Name Like "*!Criteria" Then Else nme.Delete End If Next nme Application.ScreenUpdating = True Application.EnableEvents = True End Sub -- Thank for your help BeSmart "joel" wrote: > > I think you know what the problem is already. Excel doesn't delete the > VBA project when a workbook is closed so the project is still active > when you run the macro a 2nd time. You always want to run the macro > from the first workbook that was opened. You should always refere to > the main workbook using THISWORKBOOK. > > > Try to Avoid using Activeworkbook. When you open a workbook use > > Set bk = workbooks.open(filename:="c:\temp\book1.xls") > > Then use bk to reference the new workbook. for some reason if you open > a text file as a workbook the statement above doesn't work. So I > immediately after opening the workbook use a set statement to make a > variable equal to the active workbook > > Set bk = activeworkbook > > > A common problem with VBA is that the focus shifts from one object to > another without you knowing it is happening. for example when you open > a workbook the focus changes to the workbook that you opened to one of > the sheet of the workbook (which ever sheet was the active sheet when > the workbook was closed). > > Without seeing your code I can't tell how to fix your problem. but is > is good practive to avoid usiong the following methods: > > 1) ActiveSheet > 2) ActiveCell > 3) select > 4) Selection > > > With VBA it is inconsistenet and some commands only work with the above > methods so that is why I say Avoid. > > > -- > joel > ------------------------------------------------------------------------ > joel's Profile: 229 > View this thread: http://www.thecodecage.com/forumz/showthread.php?t=187201 > > http://www.thecodecage.com/forumz/chat.php > > . >
From: joel on 13 Mar 2010 07:20 You are getting me confused!!!!! The code below is adding a new sheet to the workbook and not a new workbook. I've made my changes to the code below to remove the methods that cause problems. I think you have to explaoin to me what is happening beofre this code is run. How do you create a new workbook and How to you open the work book. Usually when people create a new work book they will use the SAVEAS method or copy a file (template file) to a new filename and then open the the copied file and make appropriate changes. If yo uuse the SaveAS you have to be a little careful of how you reffernce the SAVEAS file since it closes the original workbook. The solution may be to use the file copy method. VBA Code: -------------------- Sub mba() Dim nme As Name Application.ScreenUpdating = False Application.EnableEvents = False With Sheets("MBA") Range("MBAData").Copy Range("A80").PasteSpecial _ Paste:=xlValues End With Sheets("MBA").Copy After:=Sheets(Sheets.Count) Set NewSht = Sheets(Sheets.Count) With NewSht .Name = "Client MBA" .Range ("A1") .Cells.Copy .cellsPasteSpecial _ Paste:=xlValues End With Range("MBApaste").Clear Sheets("MBA").Visible = False Sheets("Client MBA").Move For Each nme In ThisWorkbook.Names 'delete named ranges not needed in new 'Workbook If nme.Name Like "*_FilterDatabase" Or _ nme.Name Like "*Print_Area" Or _ nme.Name Like "*Print_Titles" Or _ nme.Name Like "*wvu.*" Or _ nme.Name Like "*wrn.*" Or _ nme.Name Like "*!Criteria" Then Else nme.Delete End If Next nme Application.ScreenUpdating = True Application.EnableEvents = True End Sub -------------------- -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this thread: http://www.thecodecage.com/forumz/showthread.php?t=187201 http://www.thecodecage.com/forumz/chat.php
From: BeSmart on 13 Mar 2010 12:29
Hi Joel Thank you for looking at the code for me. I don't need to "open" an existing workbook - the workbook is already open and the user is on the worksheet that has the MBAData named range that needs to be copied into the hidden worksheet MBA. i.e. Range(MBAData).Copy (this happens on the current worksheet - not sheet("MBA") I also don't need to open a new workbook The code "moves" the "Client MBA" worksheet into a separate workbook (to do this manually I would go right mouse, select Cooy/Move, select "new booK" & hit OK. i.e. Sheets("Client MBA").Move I tried to test your code (after a few little changes - see below revised code), but had these problems: - it didn't select worksheet MBA to pasted MBAData values from A80 - it pasted the values into the current worksheet from cell A80. With Sheets("MBA") Range("A80").PasteSpecial _ Paste:=xlValues End With - it didn't change the worksheet name "MBA(2)" to "Client MBA"? With NewSht ..Name = "Client MBA" ..Cells.Copy ..Cells.PasteSpecial _ Paste:=xlValues Thank you for your patience and help BeSmart Revised code: ____________ Sub mba() Dim nme As Name Application.ScreenUpdating = False Application.EnableEvents = False Range("MBAData").Copy Sheets("MBA").Visible = True With Sheets("MBA") Range("A80").PasteSpecial _ Paste:=xlValues End With Sheets("MBA").Copy After:=Sheets(Sheets.Count) Set NewSht = Sheets(Sheets.Count) With NewSht ..Name = "Client MBA" ..Cells.Copy ..Cells.PasteSpecial _ Paste:=xlValues End With Range("A1").Clear Range("MBApaste").Clear Sheets("MBA").Visible = False Sheets("Client MBA").Move For Each nme In ThisWorkbook.Names 'delete named ranges not needed in new 'Workbook If nme.Name Like "*_FilterDatabase" Or _ nme.Name Like "*Print_Area" Or _ nme.Name Like "*Print_Titles" Or _ nme.Name Like "*wvu.*" Or _ nme.Name Like "*wrn.*" Or _ nme.Name Like "*!Criteria" Then Else nme.Delete End If Next nme Application.ScreenUpdating = True Application.EnableEvents = True End Sub |