I've tried declaring my object variable (TabOrder) as module-level within a Sheet Module and also as local within a Subroutine in the Sheet Module. In both cases, when the debugger steps through the code, the object is destroyed almost as soon as it is created.
View 4 RepliesI'm trying to attach Worksheet_Change to a worksheet created at runtime. Usually you put it in the code window of the Sheet object but what do you do when you create the worksheet at runtime?
I'm getting the following error:
"Object library invalid or contains references to object definitions that could not be found"
I wasn't getting that error last night and I'm not sure what I may have done to cause this error.
It seems to be cause by code running on one sheet of my workbook, but I'm not really sure about that. I'm still a bit of a novice at VBA.
I'm using Excel 2002 SP3 and I'm running MS XP Home as my OS.
Do you have any ideas what can cause this error and/or how to trace down the offending objects/code?
1) i have office 2003 on a laptop. within powerpoint, i can create a 'microsoft excel chart 11' object. to create a link to the excel data source, do i have to go through the odbc sql setup? it works, but i don't want my powerpoint to be dependent on some excel file somewhere. what are the other options to insert/make a functional pivot chart in powerpoint with the data also within powerpoint? the data as sheet option does not result in the chart being a pivot, it's just a plain chart. it has to be a proper object, not an image paste or a chart that updates links with the excel file open.
2) i have office 2007 on my other laptop. i can not find any suitable object to choose from to make a pivot chart in powerpoint. what's the best way to go about in 2007 version?
3) am i going about this the wrong way with the objects? should i be after vba code?
I have some VBA code that crates a PDF saves it in a direcory gained from Cells in Sheet and i now wanto to email that saved PDF but it keeps coming up with file path not recognised or found.
Code:
Sub saveandclose2()
Dim MyPath As String
Application.DisplayAlerts = False
[Code]....
I have created a pop up calendar using VBA with the help of VBA Tips at www.fontstuff.com/vba and it works very well. However it has the default date of 12 March, In the tutorial that i used there was some code to use todays date if the cell (in which i was going to put a date) is empty. Which is as follows
Private Sub UserForm_Initialize()
If IsDate(ActiveCell.Value) Then
Calendar1.Value = DateValue(ActiveCell.Value)
Else
Calendar1.Value = Date
End If
End Sub
yet when the calendar does pop up, the date remains at 12 March (the calendar in the visual basic editor reflects the date correctly). I've tried to change numerous amounts of properties, without success
When you use a basic function like SUM, you see SUM(number1,[number2],...). The "number1,[number2],..." using the function.
Now when using a function you create (e.g. below)
VB:
Function euro(dolar) As Double
euro = dolar * 1.2
End Function
When I type =euro in a cell in excell, i dont get the indication euro(dollar).
Is it possible to activate this option in a way or another?
I've created a macro which copies the current tab to a new workbook (Book X):
[Code]....
I then manipulate the copied data in Book X. Once this is done I move the manipulated sheet back into original file
[Code] .......
The problem i'm having is I'd like to close the newly created Workbook X file once the macro has run but Workbook X is usually called Book2, Book4, Book7, Book32 etc.
Workbook X will always be the previous file if i use the ALT TAB shortcut.
I am trying to create a function in VBA that will return a range, but I don't want the range to point to cells on a spreadsheet. I want the range to return a vba created range. Is this possible or impossible? Is a variable of type range required to point to cells on a spreadsheet?
I originally posted this in the "Excel New Users forum" - i guess that was an error, but I'm very new (second post) and very new to VBA in general - so please be gentle!!
I've created a macro which opens a workbook, creates and renames a new worksheet, and moves it to the end of the workbook.
I then need to paste into this new worksheet a selection from another workbook.
How would I specify in the code that the selection needs to be pasted into the newly created tab?
I am trying to create a file which will contain several thousand links to files which will not be created for some time. It's purpose is to trend KPI's and budget compliance over time. Is there a way to create these links in Excel 2007 without having to cancel the annoying "Update Valeus" window? Here is an example of that is looking for a file that will be created in 2 weeks.
=IF(AND(ISNUMBER(MATCH(B12,'V:MaintenanceWork ManagementPublicWork Management KPIsSAP SchedulingJobs With Operations In The Past[Jobs with Operations In The Past 11-4-09.xlsx]Plant Section Sort'!$B:$B,0)),(ISNUMBER(MATCH(I12,'V:MaintenanceWork ManagementPublicWork Management KPIsSAP SchedulingJobs With Operations In The Past[Jobs with Operations In The Past 11-4-09.xlsx]Plant Section Sort'!$I:$I,0)))),"Duplicate","New")
I'm attempting to enter a formula into cells on a spreadsheet through a macro.
I want the formula to read:
=if(isblank(G1),"",month(G1))
but because this is being created within a macro it doesn't like the double quotations.
I can get it to display "0" but i want those cells to be NULL. Right now my only idea is to make an IF statement in the code that doesn't insert a formula in those instances, but I'd like to keep them all as formulas for later changes. Anyway around this dilemma?
Example Code (only a piece):
Code:
With Range("F1")
.Value = "=if(isblank(G1),,month(G1))"
.AutoFill Destination:=Range("F1:F" & lastrow)
End With
I'm working on a Tool, where every change should be saved in a new Version. So if the user changes any number, he should click my selfcreated save-button, which takes todays date in the filename (no problem so far).
I tried to disable the "normal" save-button with:
Code:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Cancel = True
MsgBox ("saving cancelled!")
End Sub
Unfortunately this code won't allow my own button to save too. Is there any other way? Maybe sth like if "normal" saving is used, then automatically activate a makro (which is also on my button) instead of just saving?
I'm a novice at Excel and need some help. We have a local area Dollars for Scholars high school chapter auction coming up and I need to sort the information that has been entered into an Excel spreadsheet (there are several hundred contacts). I need this sorted to create labels to send out donation letters. Last year we used ACT, but the chapter board wants to use Excel so everyone can have the info at their fingertips (since everyone doesn't have ACT). The information was entered in column A like the following (in rows not columns):
Name
Address 1
Address 2
City, State
telephone #
Name
Address 1
Address 2
City, State
telephone #............
I've created and excel spreadsheet that has code like this in a Module named Print w/ in the workbook:
Sub Print_wsPrint()
Sheets("Print").PrintOut
End Sub
I have saved it as an MS Office Excel add-in.
I added the add-in to my excel environment via Tools, Add ins...and let it copy it to my C:Documents and SettingsjohnApplication DataMicrosoftAddIns directory.
But yet when I create a menu button and assign the Print_wsPrint to a custom menu, it complains and says "The macro Model.xls!Print_wsPrint cannot be found," where Model.xls is the workbook I am in. It's in my add-in, not in the current wb.
How do I get a random name from a list that I have created? The random feature only deals with numbers. I want to be able to push F9 or click on something and have a cell randomly pull a name from the list.
View 9 Replies View RelatedI need to create a batchfile from data retrieved from an excel spreadsheet.
1. I have 30 pre-select users on a list with their phone numbers and want to create checkboxes on each user to be selected.
2. Selected users will then be included in a batchfile to execute but only the corresponding phone numbers will be used.
3. Probably need to use a form for it to be user-friendly...i guess.
e.g. batchfile.bat
@ECHO OFF
::Location of the executable file:example.exe
d:
::CLI of utility
example 32165487 -tsve -g<ip> <msg>
example 45679821 -tsve -g<ip> <msg>
example 16543248 -tsve -g<ip> <msg>
I've created an add-in that runs a simple macro. The problem I have is that when I run the add-in it opens the workbook I originally created it from - is this what I should expect it to do? How do I stop this from happening?
Also, do I need to have the workbook from which I created the add in located in the same place on every machine I want to use the add in on? At the moment if I move the original workbook the add-in won't work.
I have recently finished working on a monthly report to count our sales but have now been asked to make a new spreadsheet to measure the trends.
In short i want to create a worksheet that will add up these figures, etc.
So far i have only 3 reports - to March - called ...
How to put a formula into my userform created in Excel.
What I have is 4 Combobox's which can select either 0,1,3 or 9 then each box has a weight which that number must be timed by this is the excel formula:
=SUM(L48*10,M48*10,N48*8,O48*6) so that was 9x10,3x10,9x8,3x6 Which gives me a sum of 210.
Can this be added to the userform so when the user selects the number from the dropdownbox it will calculate it into the total score?
This is a screen shot of the userform : Capture.JPG
I have created a toolbar with buttons that run macros. When the macro is assigned to that button the macro will run fine. However, when the file name of the excel workbook has changed (file relocated or name changed) then the macro (button in the toolbar) does not work and has to re-assigned in the toolbar. Is there anyway of getting around the problem of having to reassign the macro to the toolbar button whenever the file name/directory changes.
View 5 Replies View RelatedI have a button that will create tabs based off of information from a text box and now I want to select one sheet and insert text into certain cells so when they create a new tab information that is generally going to be there is autogenerated. I have an example of my code below. Please point me in the right direction cause I cant find anything really helpful that I havent tried. Everything is under the "gateway" then "New tabs" and PBG-Activity list is the only one with this macro.
View 2 Replies View RelatedI have the following procedure that creates sub folders for items in a named range (Room) in the parent folder (Pictures). The procedure works as expected; a sub folder is created for each item in the named range. What I'd like to do is give the User feedback as to what sub folders were created. This is the code I'm using:
View 3 Replies View RelatedI have the following code that creates a new workbook and names it after the value of a cell in another workbook. Another part of the macro will switch back and forth between the original workbook and the newly created workbook.
What is the best way to refer to new workbook? I'm familiar with the "workbooks "VARIABLE NAME".Activate" style but, the name of the new workbook will change every time the macro runs and I'm not sure how to refer to it.
the code below.
HTML Code:
Sub MakeNewBook()
Dim wB As Workbook
Dim nPath As String
[Code]....
How many sheets can be created in Excel 2010? Is it depend on memory PC or is any limit within Excel?
View 2 Replies View RelatedI have created a spreadsheet that I wish to save as a template. However, when the a new workbook is created from the template it needs to be saved as a Macro Enabled workbook not just a workbook which is the default. To achieve this I added the line 'thisworkbook.FileFormat=xlOpenXMLWorkbookMacroEnabled' to the 'beforesave' macro for the workbook. This works fine except that it makes saving the file as a template quite difficult.
How I can trap the first time a workbook created from a template using New is saved would be most useful.
I would like to name a shape in excel upon its creation ...... I Can name the"line" in the macro....But I would like every other line created after its Name to be"numerated or given a unique identifier while keeping its parentname "Jimmy". I believe it has to do with a "eventHandler " ! code attached below....... Like"Jimmy1" "Jimmy2" etc
Code:
SubNeave_Click()
ActiveSheet.Shapes.AddConnector(msoConnectorStraight,400, 150, 800,150).Select
Selection.ShapeRange.Line.BeginArrowheadLength =msoArrowheadShort
Selection.ShapeRange.Line.BeginArrowheadStyle =msoArrowheadStealth
[Code]...
I am having a excel vba code that copies tab from different excel file and then i want to use in sql query. It is always giving me error saying that "The Microsoft Access database engine could not find the object 'rahul'. Make sure the object exists and that you spell its name and the path name correctly. If 'rahul' is not a local object, check your network connection or contact the server administrator."
Though, when i rerun the macro after reopening the excel file, it is working fine. Seems like tabs dynamically created cant be used. Is their any way to get it done at runtime? I want to delete new created tab once data from new tab is inserted to required tab.
my macro calls first copyAllSheets(filename) and then writeSheet().
Code:
Private Function copyAllSheets(ByVal fname)
Dim destSheetCount As Integer
Const FUNCNAME = "copyAllSheets"
On Error GoTo ErrorHandler
Set wb = Workbooks.Open(fname)
destSheetCount = ThisWorkbook.Worksheets.Count + 1
wb.Worksheets("Buyer").Copy after:=ThisWorkbook.Sheets(1)
ThisWorkbook.Worksheets(destSheetCount).Name = "impBuyer"
[code]........
This is part of code so probably it wont be easy to understand the flow. but mainly i want to know how can i have newly created tab as table_name.
I created a button that I have created from a form but I cant seem to delete it or cut it. It just remains there, is there any way out of this?
View 9 Replies View RelatedI am using Excel 2003 and have created a spreadsheet in which one column has text. I have created a second spreadsheet and I am trying to use vlookup to bring in the text from spreadsheet number 1 to spreadsheet number 2. Some but not all of the text comes over-it just stops at certain point. Has anyone else experienced this and is there a solution?
View 9 Replies View Related