Replies

If you give the checkboxes useful names, such as Checkbox93 for the one in row 9 column 3 of your set of boxes, you can automate the references:

Sub tryit()
Dim sh As New Worksheet
Dim iRow As Integer, iCol As Integer
Dim ChkBoxValue As Boolean
Dim totalDollars As Double

Set sh = Worksheets("Sheet1")
totalDollars = 0
'Run through the checkboxes by rows and columns
For iRow = 1 To 9
For iCol = 1 To 4
    ' Get the value of the checkbox in row iRow, column iCol
    ChkBoxValue = sh.OLEObjects("Checkbox" & iRow & iCol).Object.Value
    ' Add up the values where the boxes are checked
    If ChkBoxValue Then
        totalDollars = totalDollars + sh.Range("a1").Offset(iRow - 1, iCol - 1)
    End If
Next iCol
Next iRow
MsgBox totalDollars
End Sub

This makes it easier to be sure you’re getting the right entries

Ian.

Sorry – I use Excel 2000 now, but I thought I remembered InStrRev being in earlier versions.

Memory going with advancing age, I’m afraid!

Ian.

InStrRev() finds a string within another one starting from the end, so you could use:

iPos = InStrRev(FileToOpen, "")
strPathName =  Left(FileToOpen, iPos - 1)
strFileName = Right(FileToOpen, Len(FileToOpen) - iPos)

Have fun!
Ian.

Thanks again! I tried the Export/Delete/Import on a smallish file and got a 20% reduction in size.

Ian.

Will the standard File Open dialog do what you need?
You can call it from Visual Basic by

Application.Dialogs(xlDialogOpen).Show

Ian.

Thanks, Jan. That works for me too! Gee you’re smart!

I’d already tried calling a sub in a normal module from the event sub in the class module – which didn’t work. Presumably the ‘OnTime’ call gets the control completely away from the SpinButton module and allows the Close to proceed properly.

Thanks again.
Ian.

Thanks!

Ian

John:

Doing a Save As with format ‘Formatted Text (space delimited) (*.prn)’ gives more or less what you want.

Ian.

(Though something like this is more fun:

Sub MakeTextList(rng As Range, strOutFile As String)
Dim nRows As Integer, nCols As Integer
Dim dProc
Dim iFieldWidth() As Integer
Dim iWidth As Integer
Dim i As Integer, j As Integer
Dim strOutput As String

Open strOutFile For Output As #1
nRows = rng.Rows.Count
nCols = rng.Columns.Count

ReDim iFieldWidth(1 To nCols)
For j = 1 To nCols
    iFieldWidth(j) = 0
    For i = 1 To nRows
        iWidth = Len(rng(i, j))
        If iFieldWidth(j) < iWidth Then iFieldWidth(j) = iWidth
    Next i
Next j

For i = 1 To nRows
    strOutput = ""
    For j = 1 To nCols
       strOutput = strOutput & (strPadded(rng(i, j), iFieldWidth(j) + 1))
    Next j
    'Probably test here for blank lines
    Print #1, strOutput 
Next i
Close #1

dProc = Shell("notepad.exe " & strOutFile, vbNormalFocus)
AppActivate dProc
End Sub

Function strPadded(str As String, iLength As Integer) As String
Dim i As Integer

strPadded = str
For i = Len(str) + 1 To iLength
    strPadded = strPadded & " "
Next i
End Function

Not sure what’s happening, but zeros seem to behave oddly.

With the following formula in some convenient cell

=SUM(IF(COUNTIF(A1:A1000,A1:A1000)=0,0,1/COUNTIF(A1:A1000,A1:A1000)))

and data only in the first 10 rows of column A as follows:

0
a
s
d
f
g
0
f
0
0

I get a value of 253.5

Odd!

Ian.

Thanks for checking, Steve.

Ian.

Yes, of course a spinbutton isn’t usually the most logical place to close a workbook. I was half expecting such a comment!

There is a reason, however! I am using a workbook to keep records of scores of a computer game. Spin buttons count wins and losses and after a certain number of games it puts up a MsgBox asking ‘do you want to quit’ . If you click ‘Yes” the workbook is closed. Except that Excel crashes instead which is a nuisance if there are other workbooks open at the time.

After steadily deleting controls and code, I discovered the source of the problem and wondered if it was known. I simplifed the code to its bare essential so there was nothing else that could be causing the problem.

Thanks for the comments!

Ian.

The DATEVALUE worksheet function seems to recognise local settings.

The attached worksheet interprets 20030301 correctly as 1 March 2003 (via 01/03/2003) when I run it with Australian settings. I’ve split the formula between B1 and C1 only for clarity.

Ian.

There’s a web site that discusses the complexities of the Chinese Lunar Calendars at
http://www.chinesefortunecalendar.com/clc/default.htm “]http:// http://www.chinesefortunecalendar.com/clc/default.htm [/url]

Have fun!

Ian.

I’m not sure that I’ve understood the question, but something like

 =if(and(isblank(A1),isblank(B1), "", SUM($G$3:G68)-SUM($F$3:F68)) 

will give a blank cell unless one or other of A1 and B1 is nonempty

Ian.

If you use Excel dates, then the x axis values are in days. Your serial numbers are in months. The number of days per month varies, so there isn’t a simple way to get from one to the other.

Ian.