Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  HOME    TRACKER    Visual Basic

Help In Menu/submenu Macro In Excel.

 guys, i need help in macro. I have a menu/submenu macro in excel

 and i want this macro to be global. I mean we are 10 member team, and i want this marco run in all system's excel, when they open there excel, this menu should be there.

If any of you guys have done this please help me.


View Complete Forum Thread with Replies

See Related Forum Messages: Follow the Links Below to View Complete Thread
Popup Menu Must Have At Least One Submenu
Hi all. Dunno what's happening here. I know someone here will know.

I have a little menu that looks like this:

...Delete Record

I want it popped up when a grid is clicked on. Problem is I'm getting the error: Popup menu must have at least one submenu.

The Edit menu name is popUpMain and the Delete Record menu name is popUpSub. I'm using the call
PopUpMenu popUpMain
to call it.....what should I be doing?

Vb6: How Do I Determine If A Menu Has A Submenu?

I'm trying to work out how to determine if a menu has a submenu. I don't think it makes a difference but I'm working with a popup menu for left mouse click on an icon in the systray. And it's VB 6.0.

Some help would be much appreciated.


Popup Menu And A Submenu
I keep getting this Run-time error when I try to use Popup menu. Implemented a Menu (top level) and a Sub level (Using Menu Editor). I thought anyway.

But keep getting this. What am I doing wrong? Actually I want to populate a popup with a description of a node when the user right clicking in a treeview, (the data comes from a dB). Is is possible to send text to the popup menu?

How To Put Icon In A Menu/SubMenu?
Any tutorials in putting icons in a Menu/SubMenu using VB6? thanks.

Adding Menu And Submenu And Runtime
I would like to add a menu that contains submenus at runtime. Is this possible in Visual Basic 6? I have used control array to add a menu but I have not figured out anyway to add submenus to these added menus.

Here is an example of what I want to do. The user can define custom report each of which can be printed, viewed, save as or updated so I would like to create a menu like.

---- Custom Report 1
-------- Print
-------- View
-------- Save As
-------- Options

Load Menu + Submenu At Runtime
Is this possible? I'm trying to load a few menu items depending on a text file (this part isn't important) where each new menu item loaded would also have a few submenu items. The submenu items are a problem, I can't find a way to load a submenu into a menu that's been loaded at runtime.

How To Add Popup Menu To Outlook Submenu From VB6

I want to add a popup menu to Outlook submenu using VB6.I already add a submenu to Outlook Tools menu,now i want to add popup menu to already added submenu.Please give me a solution for that,thanks in advance.


Add / Remove SubMenu To Existing Menu During RT
Hi how can I add an menuitem to an existing menu during RT.
Like in VB IDE the Windows menu.
Actualy I have to do exactly what the Window menu is doing.

Thanks in advance

How To Add Submenu In System Menu Like Winzip8.1
i want to add sub menu to windows explorer right click menu
(when right clicked on any folder or file) like winzip8.1 does
(shown in attachement : untilteld.jpg)

How To Add A Submenu In A Menu That Is Created At Runtime
can any one tell me if we can add a submenu to a menu that is loaded on runtime...ive created a menuitem "type" at runtime and now i want to add a submenu to this menuitem."type" menuitem doesnt exist at design time!

Excel Macro To Disable File Menu

I am wondering if it would be possible to make invisible/disable drop down menu items (From File through Help) on Excel spreadsheet. It would be okay to attach a Macro to the spreadsheet if the macro does this job for me.

If you have any suggestions, I would greatly appreciate.


How To Add Submenu In Windows Explorer's Context Menu(right
i want to add sub menu to windows explorer right click menu
(when right clicked on any folder or file) like winzip8.1 does.

plz see the attacment picture to understand my question.
(shown in attachement : untilteld.jpg)

Problem About Adding SubMenu Under An Existing Menu:(
I've studied for quite a time about menu APIs under VB6. Yet still not knowing how to add some subMenus dynamicly under a menuItem. My purpose is that when the app loads, the app reads from db about my "Process number", and add the number of "Process" submenuItems under a menuItem. It seems like the structure below:

VB Code:
SystemMenu        SubMenu   'Displays on the control bar of the form             Item1             Item2             Item3                    AddedItem    'The menuItems to be added here             Item4             Item5             ......

I don't know whether I have described clearly. I know how to add items under the SubMenu, yet not aware of how to add items under an Item, sigh~. Hope you guys help me~~~I have dreamed of my bed for a day, yet now have to face that troublesome app, sigh~~~

Adding Menu && Submenu Items At Runtime
I've seen lots of posts on around here with poeple trying to figure this one out.

My goal is to create a menu at runtime based on a file containing it's layout specs. This will enable me to add or remove items and submenus any time I want. MIRC uses this exact idea in it's popup menu for the chat. Those are configurable.

It's pretty easy to add only menu items, but I can't seem to figure out how add submenus and populate them. Do I have to resort to API for this?

Menu And Submenu Style In A Dropdown Combo Control?
Hi, I have a combobox with over 100 items which is inconvenient for a user to choose an item. So I would like to group the items into several groups and just put group name into combobox list, whenever user click the group name, I hope another temporary window pups up beside the dropdown list to show all items in that group. Can I do it? Thank very much for your help!

Using APIs To Create Submenu Or Submenu Array ?
I know the code to create a popupmenu and fill it with items. Here is the code:

Public Declare Function CreatePopupMenu Lib "user32.dll" () As Long
cbSize As Long
fMask As Long
fType As Long
fState As Long
wID As Long
hSubMenu As Long
hbmpChecked As Long
hbmpUnchecked As Long
dwItemData As Long
dwTypeData As String
cch As Long
End Type
Public Declare Function InsertMenuItem Lib "user32.dll" Alias "InsertMenuItemA" (ByVal _
hMenu As Long, ByVal uItem As Long, ByVal fByPosition As Long, lpmii As _

Dim hPopupMenu As Long
Dim retval As Long

'inside an event procedure

hPopupMenu = CreatePopupMenu()
With mii
.cbSize = Len(mii)
.dwTypeData = "&About This Example..."
.cch = Len(.dwTypeData)
End With
retval = InsertMenuItem(hPopupMenu, 0, 1, mii)

This will produce an menu item for popupmenu. But I tried to change the ftype, fmask value to create submenu or submenu array within popupmenu without success.
Anyone could help me?

Converting An Excel 2002 Macro To An Excel 95 Macro
Hi there, Iíve finally got my macro program to work (thanks everyone). Now I have to load the macro on a much older machine. I wrote the Macro on Excel 2002 and now Iím trying to run it on Excel for Windows 95 Version 7.0. Iíve loaded it up, but there are all sorts of errors. Does anyone know of a simple way to convert the macro code so it will run smoothly on Excel 7.0?

Openning Excel And Running Macro From Word - Macro
Ok - I have a DB which I export to Excel, I then run a macro to "Groom The Data". I then run word and run a Macro (MailMerge to print labels) which retrieves data from the excel spreadsheet groomed above. Everything is working fine, despite my novice knowledge of VB, but I would like to do this in a single click.

I use a command line shortcut to Launch word and automatically have it run the macro, but I still have to Launch Excel and manually run its macro first.

I did not find a command line switch to make excel launch and run a macro. Is there any way I can Lauch Word have its Macro link to excel, then launch and run the excel macros before performing the macro I have set up in word (mailmerge)

In Other Words I have working macros in Word and Excel, I want the Word Macro to force the Excel macro to run before completing the rest of its duties.


Run Access Macro From Excel Macro

I am using Microsoft Office 2000 and trying to run an Access macro from an Excel macro, but my Excel macro does not seem to be working. When I run the Excel macro, nothing happens - I do not get an error and the code does not do what it is suppossed to. Below is my code:

Sub DisplayForm()
' Initialize string to database path.
Const strConPathToSamples = "C:Miscell est.mdb"

strDB = strConPathToSamples & "test.mdb"
' Create new instance of Microsoft Access.
Set appAccess = _
' Open database in Microsoft Access window.
appAccess.OpenCurrentDatabase strConPathToSamples
' Run Access macro.
appAccess.DoCmd.RunMacro "Macro1", 1
End Sub
I have included the references to "Microsoft DAO 3.6 Object Library" and "Microsoft Access 9.0 Object Library."
Any help on this subject would be much appreciated.

Lotus Macro To Excel Macro Please Help!!
I know nothing about Lotus but I need to convert Lotus macro to Microsoft Excel. Can anyone help

Recreate A Macro Menu
I create my own menu with sub menu in Excel 97 and they are all associated with macros. Now, I want to put this menu on other computers but their's too much sub-menu to recreate them all manually, so I tried to make a macro witch make those menus, but it doesn't work. When I execute the macro, the menu is blank and the macros aren't associated with the menus. When I look to the generated code I see that:
Application.CommandBars("Worksheet Menu Bar").Controls.Add Type:= _
msoControlPopup, Before:=11
Application.CommandBars("Menu contextuel personnalise 1").Controls.Add Type:= _
msoControlButton, ID:=2949, Before:=1
Application.CommandBars("Menu contextuel personnalise 1").Controls.Add Type:= _
msoControlButton, ID:=2949, Before:=2

So there's no rename action and no associated macro.

I could program in VB but I know nothing in VBA.
(I use a French Excel 97)

Add Menu Item To System Menu Of Excel Imported Form [SOLVED]
I'm trying to achieve above 2 items but can't find out how. I've searched this forum already but also can't find anything relevant so am hoping that some of you might be able to kick me in the right direction.

I'm creating a combined VB6 and excel application. Basically VB6 controls excel. The forms used are imports from existing excel applications where I didn't feel like recreating them all over again. Part from that, the excel tab control is way better and easier to use then the VB6 one.

Progress timer:
When starting the program I want to show a progress timer because the program needs to initialize many items and arrays used. I thus created a simple progress timer which depending makes certain pictures visible which form a simple progress bar. The timer used is the VB6 timer option since there's nothing relevant like that from excel. This works well in itself but the problem is with display of the timer. During start it shows the form on which it was created but the timer only starts working after all has been initialized which basically defeats its purpose. Does anybody have any idea how to conquer this one.

Add item to top left window menu:
I want to add a About option to the top / left menu of my program window, the one also containing the minimize and maximize options. I've seen it done in other programs and since it is the only menu item I need I would prefer to do it this way also. I can't however find any information on how to go about this. Hope that someone can give me a push in the right direction for this one as well.

Thanks in advance for any suggestions.

Best regards,


Disable The Save Menu Item In The File Menu For Excel .
Hi all,
Please let me know how to disable the save menu item in the file menu when a excel sheet is opened.

Hiding From Macro Menu A Rotine

It's possible to Hide a public sub of VBA from the Macro Menu.
It's possible to call a private rotine ???

Normaly, I use the following sintax to call public sub rotines


Call [NAME]

But in the Tools->Macro menu, this rotine apears to. I want this rotine runs only when the VBA call's...

Assign Macro To Right Click Menu
I want to know how can assign a macro to right click menu. Do better understand in Word, you click your right mouse button in a document you see a menu, when you select a word, you click your right mouse on it, you also see a menu. I want my macro to appear on the menu. This is exactly what I want. When I right click in any part of the window. I want my macro to appear on the menu. For example, if you select a word you right click on it. You see font, conpy, etc. I want my macro to be one of them.

Excel VB Macro Invoked By Customized Button Not Moved When Excel File Is Copied && Pasted
hello....the thing is I wrote a macro in an excel file which get's invoked when a customized button is when I copy and past this excel file, the button in the new excel file still keeps pointing to the macro in the old file and only the macro in the old file is invoked again.....can anybody tell me how to resolve this problem?

Adding Custom Menu To Excel 2000 Menu
Dear All,

I have recently started developing Excel 2000 macro. Can anyone give me a idea or a small piece of code regarding how can i add a new menu to the custom menu bar at top of the Excel Menu and also to the popup menu that appears when we right-click any cell on the worksheet.


Possible To Launch A UserForm In Excel From Startup Macro Without Showing Excel?
Greetings to all VBA/Excel Excperts!

I have a project at work that is requiring me to utilize Excel/VBA (something I'm not super-conversant in) and I am wondering if it is possible to have a UserForm be the first thing that pops up when you launch (i.e. double click on) a spreadsheet (likely an .xlt but not necessarily)? I know I can have an Autostart Macro (or something of that ilk) that could launch the form, but I am hoping there is a way to do this so that it just looks like I launched the UserForm and am not firing up the entire Excel program.

As soon as the functionality encapsulated in the UserForm is completed it will shut down Excel, which is why I'd rather just have the form and nothing else.

My reason for doing this is to make deployment of this "application" simple - just drop the spreadsheet onto the target system (which of course must have Excel 2003 installed, which all of our company systems do) and double click. Saves having to create an install package for a plain ole VB app.

Thanks for any suggestions and/or taunts you may have!

How To Create Report In Word Format Through Excel Or Excel Macro
I want to create one Report in Word document Format through Excel.
I have some Data in excel.Now How can I create report in word format using Macro or any other feature, if yes then How ??
Can anyone help me in solving this.
Thanks a ton !!!

Want To Write An Excel Macro That Automaticaly Closes Excel When Finished.
HELP!!! I am trying to get excel to close itself down after a macro has run but have no idea how to do it. I can get it to save&close the current spreadsheet but thats it. Can anybody please help?

Whats The Vb Code To Close Excel Within Excel Using A Macro
i need the code to close excel within excel using a macro and saving all of the changes.

please help//


Emailing Excel Files Via Outlook Using An Excel Macro
Ok maybe this is an easy one for someone out there.

My goal is to write a macro within Excel to automatically grab a file, attach it to an email and send it from Outlook. I know this can be done. I currently have a copy of a macro that will do this exact sequence but through Lotus Notes instead of Outlook. My hang up has to be with the communication between the two programs.

This is what I have currently (Excel to Lotus Notes):

Const embed_attachment As Integer = 1454
Dim session As Object
Dim db As Object, doc As Object, ws As Object, rtitem As Object
Dim CurRow As String
Dim Recipient As Integer
Dim special As String, location As String, ext As String


'Establishes Notes session using server and .nsf file
'Server and sender must be the person sending the email
server = Cells(2, 2)
Sender = Cells(3, 2)

Set session = CreateObject("Notes.NotesSession")
Set ws = CreateObject("Notes.NotesUIWorkspace")
Call ws.opendatabase(server, Sender)
Set db = session.GetDatabase(server, Sender)

'Begins the mail loop for the number of recipients
Recipient = 8
send_to = Cells(Recipient, 2)
Set doc = db.CreateDocument()
doc.savemessageonsend = True
subject_out = Cells(4, 2)
Message = Cells(5, 2)
Message2 = Cells(6, 2)
special = Cells(Recipient, 3)

doc.sendto = send_to
doc.Subject = subject_out
doc.returnreceipt = "1"

Set rtitem = doc.CREATERICHTEXTITEM("Body")
Call rtitem.APPENDTEXT(Message)
Call rtitem.ADDNEWLINE(2)
Call rtitem.APPENDTEXT(Message2)
Call rtitem.ADDNEWLINE(3)

location = "h:Monthly BudgetsColumnarxls"

'Reads filenames and attaches files until it reaches the end of the file list
z = 4
file = Cells(Recipient, z)

Call rtitem.EmbedObject(embed_attachment, "", location & file & ext & ".xls")
z = z + 1
file = Cells(Recipient, z)
Loop Until IsEmpty(file) = True

'Sends file, resets doc variable to empty, and advances to next recipient

Call doc.Send(False)
Call doc.Save(True, False)

Set doc = Nothing
Recipient = Recipient + 1
send_to = Cells(Recipient, 2)

Loop Until IsEmpty(send_to) = True

'Sets variable to empty to save on memory
Set session = Nothing
Set db = Nothing
Set ws = Nothing
Set rtitem = Nothing

End Sub




Adding And Destroying A New Menu In The Excel's Menu Bar

I'm developing an Add-In (xla file). How can insert a new Menu with 4 options in the Menu Bar from Excel and when Excel is closed to delete this ?.

Best Regards


Delete An Excel File With An Excel Macro

Did any know if I can delete an excel file with an excel macro? (the file is not the ones that I am, instead is one in the same directory).

Any suggestions I'll appreciated it

Question From Excel Or Excel Macro Gurus

i really want to now that is there is any utility/way or wizard through which i can make my interface on Excel just like this
here i m trying to display my interface which wil be on sheet or user forum

TextBox1 Textbox3
TextBox2 TextBox4

Button--- Add ---.Display result in TextBox4

and Run time Change on TextBox3[that will automatically add textbox1 & textbox2 and display in [Textbox3]

but i want to do this without coding is their is way or wizard in Excel Macro to Do this Please Confirm me this thing is it possible without codind i just want to do this without coding

Somebody told me that through Excel we can do this as Front Page Make WebPage & made their code automatically

Please Reply me about this Question

Thanks in Anticipation


Create Excel File And Execute Excel Menu Command To Email It
I am using this code in VB 6 to create an Excel file and execute the
keystrokes to open the Send to message window in Excel.

Dim objExcel As Excel.Application
Dim objWorkbook As Excel.Workbook
Dim objSheet As Excel.Worksheet

On Error Resume Next 'ignore errors
Set objExcel = GetObject(, "Excel.Application")
If Err.Number <> 0 Then 'If Excel is not running then
Err.Clear ' Clear Err object in case error occurred.
' On Error GoTo ExcelProb
Set objExcel = CreateObject("Excel.Application") 'run it
ExcelWasNotRunning = True 'so i can shut it down
End If

Set objWorkbook = objExcel.Workbooks.Add
Set objSheet = objWorkbook.Worksheets(1)
objSheet.Cells(1, 2).Value = "write some stuff"

objExcel.SendKeys "%FDA", True

I want to be able to write to the new workbook from my VB app and
then allow the user to email it. The SendKeys doesnt seem to be doing
anything. How do I activate the workbook and then have the
"Send to..." message dialog box appear??

any help is Greatly appreaciated!!!


Excel - When Userform Visible, Excel Menu Unable To Be Accessed
I have an excel template that has a pop-up userform to enter different types of data. The issue is, when the form is open, I can no longer access the excel menu-bar. I would like to have this ability so that the user may copy and paste and infomation that maybe on another workbook open in the same window. How do I get this access back when the form is open?

Excel Macro Help
I'm trying to write a macro so that when you start a new row a 'N' automatically goes into column E. A friend of mine asked me for help and for the life of me I can't figure it out.

Any help is appreciated, Thanks!


Is This Possible? Excel Macro
If I wanted a user to open a particular file (name known to user) located in a particular folder could I just have them type the filename in an excel cell eg - they would type "thisfile" and then have a macro button which will open the file.

would it perhaps look something like this?

Sub OpenCellValue()

Workbooks.Open Filename:="C:MyFolderSubFolder[Range("A1").Value].xls"

End Sub

Obviously the code above doesn't work at all. Am new and stuck - hope someone can help


Help With Excel Macro

I need to create a macro to peform the following tasks:
1) Open a file dialog pop up to ask user to select the
Excel file(s). Files will have different names, but same
2) Copy certain cells in all files selected and paste into
an Excel master file called "Master".

Here are the example of the 2 Excel files data and format:
1) Opened files format, could be called Data1, Data06, etc.
1 Date: 5/2/03
2 Requester: John
3 Buyer: Mary
4 Issue: Defective parts
5 Ship: 5/8/03

2) Master file:
1 Date Requester Buyer Issue Ship
2 B1 D2 C3 B4 C5
3 B1 D2 C3 B4 C5
NOTE: Row 2 is from file Data1, row 3 from Data06, etc.

SUMMARY: Dialog to open selected files, copy certain cells
and paste/transpose over to the Master file, where it will
put the data in the first file into row 2, 2nd file data
into row3, etc.. THANKS

VBA For Excel... Or A Macro
I have to do a spreadsheet at work, well its using Quattro Pro 10, which does use VBA, but also in Excel XP.

See, its going to be like a large... Index for files on the hard drive, as well as a spreadsheet to sort "done", "in progress", Creator, division, etc.

Its going to be a huge listing of files. Right now I'm seeing about 600+ and more in the future. We want to do html links to a relative path on the disk to them, and that works just fine. Except for having to open a dialog box, travel down the path and type in the 'shown text'.

I tried to just use html in the cell, and thats a no go.

Right now all of the files are <groan> in directories by dept and then by 'done', 'in progress', 'not started'. My first idea is to get rid of that crap. Just throw it all into dept directories. If its 'not started' and theres a html link to it and someone moves it, the link is broken.

I suppose the question is, does VBA have a way to just run through the path relative to the spreadsheet and grab all of the files throwing them into html links with the path+ file also as their visible name?

A quick way to begin this would be helpful, and a quick way to update things down the road would help too. It just nagged me as something that shouldn't be so hard, but was just made needlessly annoying, and there HAD to be a more simple solution.

Thank you for you help/advice

Need Help With Excel Macro
Hi, I'm new to this forum. Here's my problem:

I have an Excel macro that deletes the entire row if the cell in the selected column is empty. I need to modify it so that it deletes the entire row if the cell in the first column contains certain text or does not contain certain other text. It seems like this shouldn't be too difficult, but I'm having a hard time with it. Here's the macro as I currently have it - any help much appreciated:

Public Sub DeleteBlankRows()

Dim R As Long
Dim C As Range
Dim N As Long
Dim Rng As Range

On Error GoTo EndMacro
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

If Selection.Rows.Count > 1 Then
Set Rng = Selection
Set Rng = ActiveSheet.UsedRange.Rows
End If
N = 0
For R = Rng.Rows.Count To 1 Step -1
If Application.WorksheetFunction.CountA(Rng.Rows(R).EntireRow) = 0 Then
N = N + 1
End If
Next R


Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub

Excel Macro
I would like to create a VB macro in Excel that will store the modification date of the worksheet in a Cell. This will allow someone who is only viewing to see how current the data is.
I tried using DateLastModified command but could not get it to work.

Any help would be appreciated

Excel Macro
I am using an Excel macro to automate a query from Access but have a bit of a problem... everything works fine when I am using the hardcoded values as selected in the query wizard e.g.

`Milestones Stuff`.`Project Ref`=3

However, what I really want to do is change these values depending upon other conditions something like this:

Dim myvariable As Integer
myvariable = 6

`Milestones Stuff`.`Project Ref`= myvariable

This doesn't seem to work at all since the query does not return any data. Can you do this kind of thing in VBA? If not what other way can you change macros dynamically?


Help With Excel Macro Please....!!
I am trying to return the file path of a specified file without the file name attached to the end of the path.
I am really struggling with interpreting the useless help documentation in VB.
I want the macro to prompt the user for a directory using this code..

pname = Application.GetOpenFilename(, , "Please Select A File In The Prefered Working Directory", , False)

This allows the user to browse the directories for the files they want but I really only want the directory, not the file itself. Unfortunately, you must select a file that gets attached to the variable, pname in this case.

This is one of the examples the help documentation shows for using the path property, one of the things that I tried...

Sub ShowFileAccessInfo(filespec)
Dim fs, d, f, s
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFile(filespec)
s = UCase(f.Path) & vbCrLf
s = s & "Created: " & f.DateCreated & vbCrLf
s = s & "Last Accessed: " & f.DateLastAccessed & vbCrLf
s = s & "Last Modified: " & f.DateLastModified
MsgBox s, 0, "File Access Info"
End Sub

Can someone tell me what filespec stands for?
I don't care about the extra info, like date modified and such.
I am only concerned with getting the path to the directory that the user has chosen, without the filename.

I have tied countless different methods to extract the directory only without the file because my code goes on to use the
.LookIn command to search the designated directory for as many files of a certain type there are, then it opens each file one by one and does stuff to them.
I just don't want to have to hard code the path to the directory in the macro script. I want the user to be able to browse for it when they run the script.

Is there an easy way to do this that I am completely overlooking??


Help With Excel Macro
I am trying to create a macro that will automatically copy a heading and paste it into the cell beside it if the cell is blank, otherwise copy the heading already in the cell and do the same.

For example:

1 Heading1 Blank Heading2 Blank Blank Heading3 Blank

"H" starts the column headings all the way to "N"
1 is the first row

I want the macro to be able to copy heading1 in cell H1 and paste it into cell I1 since it is blank. Then it goes to cell J1 and notices the cell is not blank, so it copies the contents in J1 and pastes it into cells K1 and L2 since they are blank. Then it goes to cell M1 and sees it isn't blank, so it copies the cell and pastes into cell N1 because that cell is blank.

How on earth do I get the macro to do this for me??? The headings constantly change and will sometimes have headings and other times will be blank.

Any help would be greatly appreciated.


Excel Macro Vb
i want to make a macro that enters the name onto the tab from a list, but when i run the macro it just returns TRUE my macro code is as follows

Sub Sheet_Name()
' Sheet_Name Macro
' Macro recorded 13/07/2005 by asiddle423
Dim x
x = Selection.Copy
Sheet16.Name = x
End Sub

Excel Macro Run From VB
I have a master excel file with a macro which opens and manipulates other excel files. The macro works perfectly when I simply open the master file and run it from there.

My Problem occurs when I run the macro from a VB app. In short... All of the macro works fine except for one line (the most important on of course).


Public Sub Retrieve_CF(Range_Refresh As String)
Dim setSheetOption As Long
Dim strSheetName As String
Dim lnRetrieval As Long

On Error GoTo Retrieve_Err
'Sheet1.Activate 'Activate the sheet where the report is located. Tho the name of the sheet is not sheet1 it can be referenced as such because it is
'the first sheet in the report

'Essbase function that is used to retieve data from the current sheet and highlights
'the range in the sheet that needs to be refreshed

setSheetOption = EssVSetSheetOption(xlTargetWorkbook.ActiveSheet.Name, 13, True) 'Use Alias'
setSheetOption = EssVSetSheetOption(xlTargetWorkbook.ActiveSheet.Name, 11, True) 'Retain Formulas
setSheetOption = EssVSetSheetOption(xlTargetWorkbook.ActiveSheet.Name, 5, 1) 'No indentation
strSheetName = xlTargetWorkbook.ActiveSheet.Name

'**********The following line doesn't work*****************
lnRetrieval = EssVRetrieve(xlTargetWorkbook.ActiveSheet.Name, range(Range_Refresh), 1) 'Retrieve Data from specified range of cells
Worksheets(xlTargetWorkbook.ActiveSheet.Name).range("a1").Select 'puts cursor in the upper left corner of the report
RetrieveCount = RetrieveCount + 1
GoTo Sub_Complete:

ErrorString = ErrorString & "Error: " & Err.Description & " #" & Err.Number & " Retrieval area:" & Range_Refresh & vbCr
Resume Next

End Sub

Of the lines above work fine with the exception of the line with the notation above it.
The function "EssVRetrieve" returns an error "The current sheet cannot be determined"

If you notice I grab the sheet name with "strSheetName " which is correct
and the functions above it work properly too.

I can't figure out why running it from a VB app would be any different than running it internaly. I even created a new VB project with stripped down code so all it does is open up the master file and run the macro. it still has the same problem.

Is it possible that there could be a conflict with VB thinking that my Master excel file is active versus the target file?

Thanks in advance for the help.


Macro In An Excel
Hi there!

How can I know if an excel file has a macro? I am using Excel Object in Visual Basic 6.0.. Kindly give me an idea in the code that I will use. Maybe there is a property in Excel that tells if the Excel has a Macro.



Copyright © 2005-08, All rights reserved