Posts by ascalese

ascalese · Dec 11th 2012

Re: Array values into a msgbox

brymer,

Let me offer you a little advise on how to get people to help you on message boards. Criticizing someone in your quest for help isn't going to make someone want to help you. You're opening line is combative. At the time I posted for assistance, the response from Bill gave me exactly what I needed to get over the hump on the code I was trying to develop. It is not his responsibility to tell me or any poster if I am following the proper rules for coding such as declaring variables. the declaration of variables is something that some very proficient coders never do because other languages do not support/require it. Even something as closely related as VBScript will allow you to declare variable but no the type.

If you are having problems, I would suggest a more cooperative approach. All capitalized words is the equivalent of screaming. Professing your experience with VBA while also asserting that you cannot figure something out again does not inspire me (and I would guess others) to want to help you. The reason is that every time I have heard about how skilled and experienced someone is, they generally don't want to hear what you have to tell them.

If you look at my first posting, I included the code that I had already attempted. It clearly showed that I gave it a fair effort. I asked for ideas on how to improve it and Bill responded. Finally, I thanked Bill for his contribution. Following basic forum etiquette will result in far more meaningful results and assistance.

Quote from brymer;639832

Here's the issue I am having with a lot posts in these forums.

Noone ever defines particular Dimensions for items or code they are offering help on.

For example, OzMVP (Bill) you don't Dim Msg As ?. Is it a string, variant, integer, etc?

I like your code, I tried to use it, but I keep getting the Type Mismatch, which means my values are not what the code is expecting.

I am NOT new to VBA, but I am very raw with Array sets.

Now that I am getting used to the construction, I am having a hard time posting msgbox's to check my tests before plugging into main code.

Display More

ascalese · Mar 20th 2007

Hi, I'm trying to create a variable whose value can be accessed from any sub/function within the workbook. Any guidance?

ascalese · Feb 27th 2007

Re: Filecopy An Open Workbook

Solved: I found this on how to use the API to copy. Works like a charm for anyone else that may need it.

http://discussion.autodesk.com/thread.jspa?threadID=534407

Code

Declare Function apiCopyFile Lib "kernel32" Alias "CopyFileA" _
(ByVal lpExistingFileName As String, _
ByVal lpNewFileName As String, _
ByVal bFailIfExists As Long) As Long

Code

Sub CopyFile(SourceFile As String, DestFile As String)
'---------------------------------------------------------------
' PURPOSE: Copy a file on disk from one location to another.
' ACCEPTS: The name of the source file and destination file.
' RETURNS: Nothing
'---------------------------------------------------------------
Dim Result As Long
If Dir(SourceFile) = "" Then
MsgBox Chr(34) & SourceFile & Chr(34) & _
" is not valid file name."
Else
Result = apiCopyFile(SourceFile, DestFile, False)
End If
End Sub

Display More

ascalese · Feb 27th 2007

I am trying to copy an excel file from one server to another. I tried to use filecopy but since the workbook is open, it returns an error.

I know, close the workbook. I wish it were that easy (unless I'm really dumb and unnecessarily complicating it). The excel doc that I am trying to copy has the code to perform the copy. I want it to copy only after other macros are executed. Any ideas on how I can get around this?

ascalese · Feb 27th 2007

Anyone know how to post to a SharePoint site using VBA?

ascalese · Feb 22nd 2007

Re: Call Private Sub From Another Sub

Thank you, that helps. To continue on that path, how do calls to forms interact with this?

Here's what I am trying to do. I have a workbook that has several macros. I want to control how a user executes them. For example, only be able to run them from a button. Any suggestions on other approaches?

ascalese · Feb 22nd 2007

Is there a way to call a private sub from another sub?

ascalese · Feb 22nd 2007

Re: Savechange:=false Does Not Stop Prompt

Got it! It is another add-in that I have installed that is invoked automatically on workbook events like close and save. I added

Code

application.enableevents = false
workbook("Test.xls").close savechange:=false
application.enableevents = true

and now it works....damn 3rd party add-in

Thanks for the help!

ascalese · Feb 22nd 2007

Re: Savechange:=false Does Not Stop Prompt

I tried using the set and close statements above, no dice. Any other ideas?

ascalese · Feb 22nd 2007

I have a workbook that uses function to check the Custom Defined Properties of the activeworkbook and compare it to the CDPs of another workbook. I open the 2nd workbook to retrieve the CDP and when I close I am prompted to save even though I have turned off alerts and tried both SaveChanges:=False, Saved = True.

Any idea what the issue is? The function (and the sub that call it) are below:

Code

Function CheckSourceVersion(strSource, strVer)


'This function is used to check the Custom Document Property (CDP) of the link


Dim lngSourceCDP As Long
Dim lngCDP As Long
Dim strSourceVer As String
'Dim strVer As String
Dim bFound As Boolean
Dim strWb As String


'Open the link
Workbooks.Open strSource


strWb = Right(strSource, Len(strSource) - InStrRev(strSource, "\"))


'Count the number of CDPs in the link source & this workbook
lngSourceCDP = Workbooks(strWb).CustomDocumentProperties.Count
'lngCDP = ThisWorkbook.CustomDocumentProperties.Count


'Check that the source has at least 1 CDP
If lngSourceCDP > 0 Then


'Loop through the CDPs in the link source
For x = 1 To lngSourceCDP
    
    'Check if the Version CDP exists
    If LCase(Workbooks(strWb).CustomDocumentProperties(x).Name) = "version" Then
        'If it exists, retrieve its value
        strSourceVer = Workbooks(strWb).CustomDocumentProperties(x).Value
        bFound = True
        Exit For
    End If


Next


'If the source has no CDPs
Else
    bFound = False
End If


'Close the link source
Workbook(strWb).Close False


'If lngCDP > 0 Then
'
''Loop through this workbook's CDPs
'For x = 1 To lngCDP
'    'If Version exists, set the variable
'    If ThisWorkbook.CustomDocumentProperties(x).Name = "Version" Then
'        strVer = ThisWorkbook.CustomDocumentProperties(x).Value
'        Exit For
'    End If
'
'Next
'
'Else
'    bFound = False
'End If


'If both workbooks have a version
If bFound = True Then
    'If the versions match
    If LCase(strVer) = LCase(strSourceVer) Then
        CheckSourceVersion = "0," & strWb
    'If the versions do not match
    Else
        CheckSourceVersion = "1," & strWb
    End If
Else
    'If the source had no version
    CheckSourceVersion = "2," & strWb
End If
End Function

Display More

Code

Option Explicit


Sub VerifyVersion()


'This sub is used to check the version between this workbook and
'the pack that this workbook is linked to


Dim strVer As String
Dim lngCDP As Long
Dim lngNumLinks As Variant
Dim lngResult As Long
Dim strSource As Variant
Dim strResult As Variant
Dim strWb As String
Dim x As Long
Dim lngNumXLSLinks As Long
Dim lngSource As Long


On Error GoTo ErrTrap


'Count the number of CDPs in this workbook
lngCDP = ThisWorkbook.CustomDocumentProperties.Count


'If at least 1 CDP exists
If lngCDP > 0 Then


'Loop through CDPs
For x = 1 To lngCDP


    'If the version is found, set the variable
    If LCase(ThisWorkbook.CustomDocumentProperties(x).Name) = "version" Then
        strVer = ThisWorkbook.CustomDocumentProperties(x).Value
        Exit For
    End If


Next


'if there are no CDPs
Else
    strVer = ""
End If


'If the version is found
If strVer <> "" Then


    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
    'Find the number of links in this workbook
    lngNumLinks = UBound(ThisWorkbook.LinkSources(xlExcelLinks))
    
    'Loop to find the number of xls links
    For x = 1 To lngNumLinks
        strSource = ThisWorkbook.LinkSources(xlExcelLinks)
        If LCase(Right(strSource(x), 3)) = "xls" Then
            lngNumXLSLinks = lngNumXLSLinks + 1
            lngSource = x
        End If
    Next
    
    'If more than 1 xls link is found
    'Show the form for the user to select the link to check
    If lngNumXLSLinks > 1 Then
        frmVersion.Show
        
        'Make sure the user did not click cancel on the form
        If frmVersion.cbFile.Value = "" Then
            'Allow the user to exit
            If MsgBox("You must select a file!" & vbCr & vbCr & "Do you want to continue?" & vbCr & vbCr & _
            "Click Yes to Continue, No to Exit", vbQuestion + vbYesNo, "Continue?") <> vbYes Then
                Application.ScreenUpdating = True
                Application.DisplayAlerts = True
                Exit Sub
            'If they do not exit, show the link form again
            Else
                frmVersion.Show
            End If
        End If
        
        'Check the version by calling the function passing the link and this workbook version
        strResult = CheckSourceVersion(frmVersion.cbFile.Value, strVer)
        Unload frmVersion
    Else
        'If only 1 link exists, pass it to the function
        strSource = ThisWorkbook.LinkSources(xlExcelLinks)
        strSource = strSource(lngSource)
        strResult = CheckSourceVersion(strSource, strVer)
    End If
    
'If the version does not exist in this workbook, exit the macro
Else
    MsgBox "The version of this workbook cannot be determined." & vbCr & vbCr & _
    "Please contact someone for assistance.", vbInformation + vbOKOnly, "Version Not Found!"
    ThisWorkbook.Sheets("Control Tab").range("B6").Value = False
    Exit Sub
End If


'Put the results of the function into an array
strResult = Split(strResult, ",")


'Used to determine the message
lngResult = strResult(0)
strWb = strResult(1)


'Based on the function return value
Select Case lngResult
    'Versions match
    Case 0
        MsgBox "The versions match!", vbInformation + vbOKOnly, "Versions Match"
        With ThisWorkbook.Sheets("Control Tab").Shapes("Step6")
            .Fill.Solid
            .Fill.ForeColor.SchemeColor = 11
        End With
    'Versions do not match
    Case 1
        MsgBox "The current version for HFM is: '" & strVer & "'" & vbCr & vbCr & _
        "This does not match the version in the link:" & vbCr & _
        "     -" & strWb & vbCr & vbCr & _
        "Please update your links to the correct version!", vbExclamation + vbOKOnly, _
        "Versions Do NOT Match!"
                    
        With ThisWorkbook.Sheets("Control Tab").Shapes("Step5")
            .Fill.Solid
            .Fill.ForeColor.SchemeColor = 9
        End With
        
        With ThisWorkbook.Sheets("Control Tab").Shapes("Step6")
            .Fill.Solid
            .Fill.ForeColor.SchemeColor = 10
        End With
        ThisWorkbook.Sheets("Control Tab").range("B5").Value = False
        ThisWorkbook.Sheets("Control Tab").range("B6").Value = False
        
    'Source link does not have a version
    Case 2
        MsgBox "The source file does not have a version!" & vbCr & vbCr & _
        "Please update your links to the correct version!", _
        vbExclamation + vbOKOnly, "Missing Version"
        
        With ThisWorkbook.Sheets("Control Tab").Shapes("Step5")
            .Fill.Solid
            .Fill.ForeColor.SchemeColor = 9
        End With
        
        With ThisWorkbook.Sheets("Control Tab").Shapes("Step6")
            .Fill.Solid
            .Fill.ForeColor.SchemeColor = 10
        End With
        ThisWorkbook.Sheets("Control Tab").range("B5").Value = False
        ThisWorkbook.Sheets("Control Tab").range("B6").Value = False
        
'    Case 3
'        MsgBox "This file does not have a version!" & vbCr & vbCr & _
'        "Please ensure that you are using the current version.", _
'        vbExclamation + vbOKOnly, "Missing Version"
End Select
        
ErrTrap:


If Err.Number <> 0 Then
    MsgBox "The following error was encounter:" & vbCr & _
    "     Error " & Err.Number & " - " & Err.Description & vbCr & vbCr & _
    "The version check did not complete!", vbExclamation + vbOKOnly, "Error"
End If
        
Application.ScreenUpdating = True
Application.DisplayAlerts = True


End Sub

Display More

ascalese · Feb 6th 2007

Re: Disable Save Button

I'd be willing to give it a shot. Can you point me to example code to remove the add-in?

ascalese · Feb 6th 2007

Re: Disable Save Button

No can do, I need the functionality of the add-in. Even if I switch it off before save, it keeps it's meaty little hooks into the workbook until after the save giving me the same problem.

ascalese · Feb 6th 2007

Is there any way to disable the save functionality of excel - specifically Ctrl+S, Save button and Save, Save As (from the file menu)?

I can't use the Workbook_BeforeSave event because as soon as excel makes a call to save (even if the call is cancelled in the event), one of the add-ins that I have installed tries to unprotect each sheet in the workbook. I am working with the vendor on a fix but until that is in place, I need to disable save and add a macro to save (the add-in doesn't get called when save happens via a macro - don't ask me, the vendor has "undocumented features" all over this add-in)

Any ideas?

ascalese · Jan 25th 2007

Re: Event Macro Causes Sheet Protection Prompts

Roy,

I am calling functions that are native to the other add-in. I have stepped through the code and haven't been able to isolate anything.

ascalese · Jan 25th 2007

Re: Event Macro Causes Sheet Protection Prompts

Simon, I think you were onto something there. I added an unprotect routine before I can the other add-in's functions. Now once the macro completes and I try to save manually, I am being asked to unprotect. Do you think it's the same problem?

ascalese · Jan 25th 2007

Re: Event Macro Causes Sheet Protection Prompts

My code is over 600 lines so I am reluctant to post it. The add-in that I am using is propietary and i do not have access to it's source.

ascalese · Jan 25th 2007

I have a macro that is attached to the Worksheet_Change event. The first time I execute this macro, it runs without an error.

One of the things it does throughout the course of the run is unprotect the worksheet, do what it needs to and then reprotects the sheet (using a password). On the second run, the sheet protection input box is shown and I am required to input the password before the code will execute.

Any idea what may be causing this and how to correct it?

fyi - The code that I have written uses an Add-in that pulls data from a database. As part of that retrieval, the other add-in protects the sheet without a password. Would that be causing my error?

ascalese · Jan 25th 2007

Re: Projects Are Still In Vbe When Workbook Is Closed

It's in the workbook. If I close excel and reopen, the project is no longer listed in the VBE until I reopen the workbook with the macro.

ascalese · Jan 25th 2007

I have been developing a decent sized macro and noticed that even when I close the workbook, the project is still in the VBE. Is this normal? If not, how can I remove it (without closing excel).

ascalese · Jan 23rd 2007

Re: Qualifying Code For Event Driven Macros

Yes I am running this from excel, sorry that I didn't specify. The macro is driven by worksheet_change

Posts by ascalese

Array values into a msgbox

Set A Global Variable Value Usable Across Multiple Subs

Filecopy An Open Workbook

Filecopy An Open Workbook

Post To Sharepoint Site

Call Private Sub From Another Sub

Call Private Sub From Another Sub

Cannot Stop Prompt

Cannot Stop Prompt

Cannot Stop Prompt

Disable Save Button

Disable Save Button

Disable Save Button

Event Macro Causes Sheet Protection Prompts

Event Macro Causes Sheet Protection Prompts

Event Macro Causes Sheet Protection Prompts

Event Macro Causes Sheet Protection Prompts

Projects Are Still In Vbe When Workbook Is Closed

Projects Are Still In Vbe When Workbook Is Closed

Qualifying Code For Event Driven Macros