Replies

I don’t think you can do this. Maybe, you can merge the cell with the cell next to it and play with that.

Sub FileNameOnly()
Dim CompleteName As String
CompleteName = Application.GetOpenFilename
MsgBox StripOffFileName(CompleteName)
End Sub

Private Function StripOffFileName(CName As String) As String
  Dim i As Integer
  Dim Length As Integer
  Dim dummy As String
  Length = Len(CName)
  dummy = ""
  For i = Length To 1 Step -1
     If Mid(CName, i, 1) = Application.PathSeparator Then
        StripOffFileName = dummy
        Exit Function
     End If
     dummy = Mid(CName, i, 1) & dummy
  Next i
  StripOffFileName = CName
End Function

(XL2000)
xla : an excel add-in file
xlb: an excel toolbar configuration file. The current toolbar configuration is stored in the Windows directory in a file named Excel9.xlb (the name may be different in a network environment)
xlc: an excel 4 chart file
xll: an excel link library filr; for example, the Analysis Toolpak add-in uses this type of file
xlm: an excel 4 macro file
xls: an excel workbook file
xlt: an excel template file
xlw: a workspace file that contains information about the windows and positions in a workspace

Other files used or recognized by Excel:
chm: a compiled HTML help file
hlp: a compiled help file (earlier help files)
dll: dynamic link library file
exe: an executable file (e.g. Excel.exe)
olb: an object type library file
txt: a plain ascii text file, readable from any text editor

This post might help you starting as well.

Sam,

As far as I know, the way to do it is making a XY-scatter chart, then doubleclick the data series and go to Y-errorbars in the data series format window. There you have a custom + and – refedit box where you place your cursor and select the errors from your column 3 (probably for the +) and from your column 4 (probably the -).

Make one chart with the desired colors on the first sheet; then copy the chart to another sheet and click on the data series, then change the range address in the chart’s series command line. If you repeat this for your other sheets, you should have charts with the same color.

Just a thought: consider loading your add-in programmatically in the case where it does not show up in the project explorer, then you might find out the reason why.

If I understand your question and based on Sammy’s last post where he talks about volumes, then I would say that you can be 100% sure that all the cups hold more than one cup as soon as the accumulated volume of the sample exceeds the maximum volume of one cup. So, take one cup at a time, measure its volume, then take another cup, measure its volume again, check if the sum of volumes exceeds the maximum volume of one cup; if not, then take another sample etc. Your sample size can be 2 (the minimum) up to the complete batch if the cups do not contain that much.

There are quite many formulas to calculate sample sizes, but it should be clear if we are talking about continuous variables (like the volume of a cup) or about counts (like the number of cups). The approach is quite different. Sample size calculations for continuous variables are indeed based on standard deviations and average values (the normal distribution; normdist and tdist are Excel’s built-in functions to use), but sample sizes for counts and proportions are based on other distributions (poisson, hypergeomdist, binomdist are Excel’s built-in functions that might help here).

e.g. the 1.96 value Sammy is using, corresponds to NORMINV(0.975, 0, 1) which is the value corresponding to a probability of 97.5% (the normal distribution is symmetrical, so to obtain a 95% confidence, you should use the 97.5% probability in the NORMINV function: (100-97.5%) + 2.5% = 5%. Using the standard normal distribution (meaning average = 0 and standard deviation = 1) you obtain 1.96.
The formula of Sammy gives the absolute minimum sample size for this kind of calculations.

Bob,

I don’t know if you mean the spinbutton on a userform or on a spreadsheet. I am not using XP (but XL2000). In the attached spreadsheet you should go to the VBA section to see a Userform with 2 spinbuttons. Each of the spinbuttons has next to it a textbox. One spinbutton can be used to increase or decrease a number in the textbox next to it (from 1 to 99), the other spinbutton can be used to go from A to ZZ, by clicking the arrows of the spinbutton.

Patty,

Can you please rephrase your question? Do you want to take a sample from the 1000 cups, control that sample and based on the obtained results say something about the complete lot? Or do you mean something different?
Typical question in quality control is: what is the sample size I need to test from a much larger lot of products, given an acceptable quality level (AQL of say 1%), with a producer’s risk of 5% and a consumer’s risk of 10%. An on top of that, how many defects should I allow in the sample to accept the whole batch?

This was the original post of Aad.

Als ik in word (XP) het dialoog venster open aanklikt opent dit venster niet op de voorgrond maar op de achtergromd, ik zie het dan knipperen op de taakbalk. Als ik er dan op klik komt hij naar voor maar word is dan afgesloten. start ik word opnieuw op dan heb ik er geen last meer van. Weet iemand hier iets meer van. Ik heb dit ook met het dialoogvenster brieven en verzendlijsten enveloppen en etiketten

He wrote it in Dutch. Below I just replied to him saying that he should post in English if he wants to get a reply. Then I have tried to translate his post.

Aad,

Als je hierop antwoord wil krijgen moet je in het Engels schrijven.

I’ll try to translate this.
Word (XP): if I click the open dialog box it opens in the background, and I see it flickering on the taskbar. If I then click it, it appears in the foreground but Word closes. If I then start Word again, the problem has disappeared. Does anybody know more about this? I have the same problem with the dialog box letters and envelopes and labels.

I hope I made myself clear, guys, because I am not a Word specialist and I am definitely not the best translator on the planet. Hope you can help aad.

Stuart is right. If you do not run the macro every day (e.g. you don’t run it during the weekends) and the first of the month falls on a Saturday or a Sunday, then the new sheet will not be created. I think that in case yesterday and today are belonging to the same month, you should check if the sheet with the month’s name already exists. If yes, everything is ok, if not, run the code as if yesterday and today were not belonging to the same month.

Or using the month’s names:

Sub test()
Dim Today As String
Dim Yesterday As String
Today = Now()
Yesterday = Now() - 1
If Month(Today)  Month(Yesterday) Then
   ActiveWorkbook.Sheets.Add
   ActiveSheet.Name = MonthName(Month(Today))
   'here I assume that the sheet with the name of the previous month already exists
   ActiveWorkbook.Sheets(MonthName(Month(Yesterday))).Select
   ActiveSheet.Cells.Copy
   ActiveWorkbook.Sheets(MonthName(Month(Today))).Select
   Range("A1").Select
   ActiveSheet.Paste
End If
End Sub

Have a look at this thread, explaining how to disable the Numlock key. You should use the Const VK_CONTROL As Integer = &H11 and should be able to disable the CTRL key.

As far as I know, there are no real restrictions to creating an add-in. Are the macros you want to run dependent on the workbook you open? That is, are the macros attached to the workbook, performing specific tasks in that workbook? Or, are they more generally performing tasks on all workbooks? Add-ins are typically quite general and it will not be easy to manage if your add-in has to do specific tasks, depending on the active workbook’s name.
Maybe you can add the macros to the TOC file first, check if everything runs as it should and then if it is still necessary make an add-in of it.