Replies

go to View >> Toolbars >> Customize then select the Toolbars tab in the dialog box and check the box next to stop recording. If you now click the little x in the top corner, the record macro toolbar disappears but will reappear next time you want to record a macro.

If I remember well, I had this problem in the past after I used the internet. Once my browser was updated to Internet Explorer 5, the problem disappeared.

One of my other questions was how to unlink the data from the original worksheet. After some experimenting I came to this (assuming I have a chart named HPChart in the activeworksheet and 1 data series):

Sub test()
    ActiveSheet.ChartObjects("HPChart").Activate
    ActiveChart.ChartArea.Select
    ActiveChart.SeriesCollection(1).Select
    Application.SendKeys "{F2}"
    Application.SendKeys "{F9}"
    Application.SendKeys "{ENTER}"
End Sub

You’ll get into trouble with this if in case of too many data, but for small data sets it works fine. F2 selects the command line, F9 unlinks the data from its range address, using the actual values as such, and enter is needed to make the whole command effective.

Just wanted to share this with you. Thanks to all!

Jan Karel,

The problem is that the template has a nice layout, contains logos, the user enters data in the spreadsheet before the calculation etc. The problem is that I didn’t thought of the allergy of the user against the presence of code in the workbook. And in a certain way he is right; after entering the data and doing the calculation, storing the output would be all that is required. Part of the output is the layout and the data entered by the user, part of it are the calculation results. That’s why getting rid of the code when saving the worksheet would be the best way to go.

I think I’ll try Andrew’s solution. It gives me a chance to learn about the VBE. Thanks anyway.

Thanks Andrew.
However, this will still make appear the “this workbook contains macros” dialog window when opening the file, as the code in the worksheet module is still there. What I hoped to do is just save the file without any code. But this does not seem possible in an easy way. As the buttons are outside the A1:N65 range, I thought to just copy this range and paste it into another (empty) workbook. Seemed to me as the most easy way to go. But then I want to get rid of the link to the original workbook in my copied chart.
Maybe your approach is the way to go. Removing worksheet code requires code. How to get rid of that?

Thanks for your reply, but this doesn’t work here.

Private Sub CmdCopy_Click()
   Dim sNameWb1 As String
   Dim sNameWb2 As String
   Dim sNameWs1 As String
   Dim sNameWs2 As String
   'original workbook and worksheet
   sNameWb1 = ActiveWorkbook.Name
   sNameWs1 = ActiveSheet.Name
   Workbooks.Add
   'new workbook and worksheet
   sNameWb2 = ActiveWorkbook.Name
   sNameWs2 = ActiveSheet.Name
   Workbooks(sNameWb1).Sheets(sNameWs1).Range("A1:N65").Copy  _
   After:=Workbooks(sNameWb2).Sheets(sNameWs2)
End Sub

I always get an object-defined or application-defined error ‘run-time error 1004’ when the copy statement is executed. Using the de######, I checked the names of the workbooks and sheets and everything seems ok, but copying does not work. If I replace After by Destination and add .Range(“A1”) then the sheet is copied but the data series in the chart are still pointing to the original workbook.

You could use an array formula like this:

=SUM(IF((A2:A4=”X”)*(C2:C4=21);D2:D4;””))

which should be entered using ctrl+shift+enter. This formula will return 75.

Here I assumed your table in A1:D4.

For each topic you need to create a new page, using page break in the word rtf file. I recommend making a contents page as well. On that page you use the titles of the topics, double underline them (to create topics) and add a hidden reference immediately after the double underlined title, like this (I don’t know how to double underline it here, but in the rtf document, be sure you used the double underline format)

IntroductionIntro

The word ‘intro’ should be placed immediately after the double underlined topic title and formatted as hidden text. This is the reference that you need to put as a footnote after the # symbol. As such, after compilation, the topic title can be used to click on and the hidden text is the reference to the page where that topic is explained.

Another hint to make your helpfile:
In the Help Workshop program, you can select ‘Map’. Here you can add the same ‘topic’ references (that is, the hidden text you used as reference) and simply put:

intro=100

This will appear as:
[Map]
intro=100

After compilation, you can force VBA to immediately jump to that specific topic, by adding the number 100 after the filename, like this

Application.Help ThisWorkbook.Path & HelpfileStr, 100

(Here I assumed that you have stored the Helpfile in the same path as your add-in and I used HelpfileStr as a string constant with the name of the Helpfile.

Hope this helps.

Mike,

Here are some interesting sites to help you create your helpfile. From the first link, you can also download the help compilers.
http://www.helpmaster.com/hlp-musthave.htm%5B/url%5D
http://www.winhelp.net[/url%5D
http://www.sinterphase.com/winhelp.htm%5B/url%5D
http://www.geocities.com/Area51/6793/helpsurv.htm%5B/url%5D
http://www.easyhelp.com[/url%5D

To set how Excel deals with missing data, select your chart and choose Tools, Options. In the Options dialog box, click the Chart tab and then select the appropriate option. Your choice will apply to all data series in the selected chart. That means, that you need to have a chart selected before you can select the options.

You can also represent data with the formula =NA() instead of leaving a cell blank. The chart will use interpolation for data cells that contain this formula, regardless of the setting in the Options dialog box.

You can make a ‘hlp’ helpfile, using Word (rtf format) and by making a project file. To do this you can make use of the Microsoft Help Workshop, which you can download free of charge from Microsoft’s website. It is not as easy as making a html helpfile, but once you understand how it works, you can make a compiled helpfile, which you can launch as a stand-alone helpfile or easily from within VBA.

you can do it in two ways:

e.g. there is a function ‘prop95ci’ with two integer arguments, defined in the add-in with the name statstartup.xla, then you can use
Debug.Print Application.Run(“statstartup.xla!prop95ci”, 35, 50)
or after setting a reference in the VBE to the statstartup add-in (via Tools >> References and checking the statstartup add-in), then you can simply use
Debug.Print prop95ci(35, 50)

End Sub

As Legare said, the way to do it is using the deactivate event:

Declare the variable CloseWB as boolean in a general module.

Dim CloseWB as Boolean

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Cancel = False
CloseWB = Not Cancel
End Sub

Private Sub Workbook_Deactivate()
On Error Resume Next
    If CloseWB = True Then
       Application.CommandBars("MyToolbar").Delete
    Else
       Application.CommandBars("MyToolbar").Visible = False
    End If
On Error GoTo 0
End Sub

When one presses Cancel when he/she is prompted with the Excel message about saving, then the toolbar is not deleted. When choosing for closing, the workbook will be deactivated, which calls the deactivate event, where the toolbar is deleted. If you are simply leaving your workbook by activating another one, the toolbar will only be hidden. Of course, you must unhide it when the workbook is activated again, using the code below:

Private Sub Workbook_Activate()
On Error Resume Next
Application.CommandBars("MyToolbar").Visible = True
On Error GoTo 0
End Sub

And of course, you can use API calls. Here is half of the solution to your problem: click here.

I am not sure about this, but if you use Excel’s VBA property

Application.ActivePrinter

this returns a string containing the name of the active printer with the connected port

e.g. HP Deskjet 930C on LPT1:

Use Instr to find ‘on’. If no printer is connected, Instr will return 0. Of course, this is language dependent.