Posts by Denis

    Sure can, here's my suggestion for your code, if anyone has any suggestions for improvement that would be great.

    Code Removed in EDIT.  See my next post for a better version

    I would suggest removing your "On Error Resume Next" statements and then try messing about with your file. I'm assuming that you had errors being raised if the menu wasn't on. I would also suggest testing to see if your control exists before trying to delete it, hide it or make it visible. This should be better than skipping the error. I believe that you could use the "FindControl" e.g. Your "Deactivate" code could look like this:

    Dim cmdBar As CommandBar
      Set cmdBar = Application.CommandBars("Tools")
      If Not cmdBar.FindControl(Type:=msoControlButton, ID:=cmdBar.Controls("Update File").ID, Visible:=True) Is Nothing Then
         cmdBar.Controls("Update File").Visible = False
      End If

    I would also not rely on your object variable "cbbNewToolMenuItem" to exist for the duration of the session. There are many things that will reset this variable. It would be best to explicitly reference the control as I have done in my example.


    Originally posted by DuckBill

    (Meanwhile, is there any way to disable the "RefEdit"-like behaviour in Application.InputBox?)


    Probably, but you would almost certainly have to use so many API calls that it would prove impractical.

    I guess that we've both learned something today then. It's not as handy as an InputBox with a RefEdit control, but it's definitely useful.

    You can program around the "Cancel" limitation quite easily:

    There's two options for you to stop the code, the one I've used completely stops all VBA. The commented out "Exit Sub" would just stop this subroutine from running.

    If you know where the cells are that have the links to the worksheet, it sounds like you want to copy those and do a "Paste Special|Values". then you can just copy the worksheet and the other references that you want to keep will be intact. This is all very doable with VBA.


    Originally posted by blue
    Are you possibly talking about the height and width of rows and columns?

    Assuming that this is the case, one point = 0.035 centimeters. (taken from VBA help for "CentimetersToPoints") Therefore:

    Row Height * 0.035 = Row Height in cm.

    I've never seen this behavior in an "Inputbox" before. Are we talking the same language here?

    e.g. In VBA you can use an inputbox to get an input from the user like:

    Public Sub MyInputBox()
      Dim strText As String
      strText = InputBox("Type something in:")
      MsgBox strText
    End Sub

    The only thing that I can think of, without being able to test it (no Excel 97), is that Excel 97 doesn't have all of those arguments for the PasteSpecial method. A quick look in the Excel 97 help file would verify this.

    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False


    I looked at your original post and my first attempt at a solution. I think I spotted some errors in my/your post. Not sure which. could you indulge me and try this code:


    You could probably condense that code by removing the Select Case statements and use a loop instead e.g.:

    Here's some code that should do as you ask:

    ...because you didn't ask for that. The code would have to be written very differently to enable that. The first line of the code:

    If Target.Count <> 1 Then Exit Sub

    Stops the rest of the code from executing if more than one cell is selected. Primarily, this is used to stop an error occurring when the user deletes a lot of cell contents at once.

    Never mind, I think I deciphered it.

    You can stick your date literal in cell A1 of sheet1. Although I think that bnix may have given you a workable solution as well.

    Chris, I have a solution for this, but I'm having a problem with your line of code:

    .CommandText = Array( _ 
    AGLTRANSACT" & Chr(13) & "" & Chr(10) & "WHERE (AGLTRANSACT.ACCOUNT>='60000') AND (AGLTRANSACT.LAST_UPDATE>={ts '2003-01-01 19:33:" _ 
            , "00'})")

    It's got an error in it and I'm having probems deciphering it. Could you post it without the long line tags "_"?

    I get the impression that you are talking about file size rather than memory required to run VBA.

    I couldn't find anything on about the subject, but then again I didn't look too hard and didn't look through the newletters.

    Quick example though. Open a new workbook and save it. Go to File|Properties and the "General" tab. You'll see the file size is about 15kb.

    Now, open the VBE and insert 10 standard modules. Save the workbook again and look at the file size. We'll be looking at about 27kb. A 12 kb increase just by adding some empty modules.

    Go back to the VBE and insert 10 userforms. Save and look at the file size, my workbook is now approximately 57kb.

    Last one, go back to the VBE and insert 10 class modules. Save and look at file size again. We're up at about 69kb.

    We can see that the code modules themselves don't take up too much storage space.

    However, if you start inserting code into the modules. Then you're going to see your file size creep up, just like in a text document created in Notepad, a datasheet in Excel and a document in Word. It's really not that hard to have so much code in your workbook that you add 500kb to the size of the file, just like it's not that hard to create a 500kb spreadsheet with data.

    Not a definitive answer, but I hope it's a start.

    If I recall correctly, and it's entirely possible that I don't, but VBA isn't installed by default on a standard Office 97/Excel 97 installation. You have to do a "Custom" install and make sure that the VBA box is checked. Failing that, since you've obviously already got Excel installed, insert your CD into the drive and choose the "Add Features" (or whatever it is called) option when the install window appears.

    Like I say, my memory is a bit fuzzy on this one. Long time, no Excel 97 install. I may even just be getting it confused with having to install the VBA help separately.