Posts by Denis
-
-
This should get you started. I've only had a cursory glance, but this sounds like something similar to what you are attempting.
-
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:
CodeDim 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.
-
Quote
Originally posted by DuckBill
(Meanwhile, is there any way to disable the "RefEdit"-like behaviour in Application.InputBox?)
DuckBill
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:
Code
Display MorePublic Sub MyInputBox() Dim strText As String strText = InputBox("Type something in:") If strText = "" Then 'Exit Sub 'Would just exit the sub and keep running any code after the call to this sub End 'stops all VBA End If MsgBox strText End Sub
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.
-
Quote
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.
-
-
What operating system are you running?
-
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.
[code]
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
[/code -
Chris,
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:
Code
Display MoreSub Macro3() Dim strMsg As String Dim datDate As Date datDate = "2003-01-01 19:33:00" 'If it works, try changing to something like Sheets("Sheet1").Range("A1").Value strMsg = "SELECT AGLTRANSACT.ACCOUNT, AGLTRANSACT.AMOUNT, AGLTRANSACT.LAST_UPDATE" _ & Chr(13) _ & "" & Chr(10) _ & "FROM AGRPROD.AGLTRANSACT AGLTRANSACT" _ & Chr(13) & "" & Chr(10) _ & "WHERE (AGLTRANSACT.ACCOUNT>='60000') AND (AGLTRANSACT.LAST_UPDATE>={ts '" _ & datDate & "'})" With ActiveSheet.QueryTables.Add(Connection:= _ "ODBC;DSN=agresso;UID=AGRPROD;DBQ=AGRESSO;ASY=OFF;", Destination:=Range("A1") _ ) .CommandText = Array(strMsg) .Name = "Query from agresso_4" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePassword = True .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = True .Refresh BackgroundQuery:=False End With End Sub
-
Chris,
You could probably condense that code by removing the Select Case statements and use a loop instead e.g.:
Code
Display MoreOption Explicit Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) Dim rngMyRange As Range, Isect As Range Set rngMyRange = Range("C4") Dim i As Integer 'define the range that you wish to work with Set Isect = Application.Intersect(Target, rngMyRange) 'define a range as the intersection of the target and your range If Isect Is Nothing Then Exit Sub 'the range doesn't exist (ie target not in rngMyRange) so exit Else For i = 1 To 5 If i = rngMyRange.Value Then ActiveSheet.Shapes("Picture " & i).Visible = True Else ActiveSheet.Shapes("Picture " & i).Visible = False End If Next End Sub
-
Does the original hard coded version that you gave us function correctly?
-
You're welcome.
-
Here's some code that should do as you ask:
Code
Display MorePrivate Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) Dim objTargetCell As Range For Each objTargetCell In Target Select Case Sh.Name Case "Sheet2", "Sheet3" Select Case objTargetCell.Value Case "AA" objTargetCell.Value = 1 Case "A" objTargetCell.Value = 2 Case "B" objTargetCell.Value = 3 Case "C" objTargetCell.Value = 4 Case "D" objTargetCell.Value = 5 Case "E" objTargetCell.Value = 6 Case "SCL" objTargetCell.Value = 7 End Select End Select Next End Sub
-
...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:
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.
Code
Display MoreSub Macro3() Dim datDate As Date datDate = Sheets("Sheet1").Range("A1").Value If Not IsDate(datDate) Then MsgBox "Please enter a valid date." Exit Sub End If With ActiveSheet.QueryTables.Add(Connection:= _ "ODBC;DSN=agresso;UID=AGRPROD;DBQ=AGRESSO;ASY=OFF;", Destination:=Range("A1") _ ) .CommandText = Array("SELECT AGLTRANSACT.ACCOUNT, AGLTRANSACT.AMOUNT, AGLTRANSACT.LAST_UPDATE" _ & Chr(13) & "" & Chr(10) & "FROM AGRPROD.AGLTRANSACT AGLTRANSACT" _ & Chr(13) & "" & Chr(10) & "WHERE (AGLTRANSACT.ACCOUNT>='60000') AND (AGLTRANSACT.LAST_UPDATE>={ts '" _ & datDate & ":", "00'})") .Name = "Query from agresso_4" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePassword = True .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = True .Refresh BackgroundQuery:=False End With End Sub
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:
Code.CommandText = Array( _ "SELECT AGLTRANSACT.ACCOUNT, AGLTRANSACT.AMOUNT, AGLTRANSACT.LAST_UPDATE" & Chr(13) & "" & Chr(10) & "FROM AGRPROD.AGLTRANSACT 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 http://www.ozgrid.com 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.