Posts by edelauna

Hello,
I am trying to write a macro for an option button, that when selected it checks to see if another option button is selected then continues to automatically change the value of 5 other option button within in 5 other group boxes

Sub SetFilter()
    'check to see if filtering is required 
    If Sheet1.Shapes("Option Button 54").value = True Then
        'set non-specific filtering
        Sheet1.Shapes("Option Button 28").Value = xlOn
        Sheet1.Shapes("Option Button 38").Value = xlOn
        Sheet1.Shapes("Option Button 44").Value = xlOn
        Sheet1.Shapes("Option Button 52").Value = xlOn
    End If
End Sub

Any help would be greatly appreciated, I think I am missing a simple concept.

Re: Error In Working With Arrays

thank you, what is vblf?

Re: Error In Working With Arrays

I am getting an error, subscript is out of range this is my code in full:
Should I be doing something to set the dimensions again?

Dim strProjects(), strMsg As String
    Dim I As Long
    For x = ActiveSheet.Range("C65536").End(xlUp).Row To 3 Step -1
        If CDate(ActiveSheet.Range("H" & x).Value) = Date And Cells(x, 3) = "1" Then
            ReDim strProjects(I)
            strProjects(I) = Cells(x, "E")
            I = I + 1
        End If
    Next x
    strMsg = "The following Projects Are Closing Today:" & vbNewLine & vbNewLine
    For x = I To 0 Step -1
        strMsg = strMsg & Chr(149) & " " & strProjects(x) & vbNewLine
    Next x
     MsgBox strMsg, vbInformation

Re: Is Date Past X Weeks From Current Date

thank you, i thought it was something simple

Re: Error In Working With Arrays

ahh ok thank you. Now how do I access these variable within the array? I am trying to do the following:

For x = I To 0 Step -1
        strMsg = strMsgs & Chr(149) & " " & strProjects(x) & vbNewLine
    Next x

Re: Is Date Past X Weeks From Current Date

The equations does not seem to work. Regardless of what the date is this if statement always runs?
I have changed the value to any date in 2006,2009, 2007, the cell is always marked red.

If ((Range("H", x) - Date) < 14) Then ' even if the symbol is > red still runs.
                red (x)

Hello,
I'm trying to create an array of strings and I thought I was doing it correct via :

:
    Dim strProjects() As String
    Dim count As Integer
    count = 1
    For x = ActiveSheet.Range("C65536").End(xlUp).Row To 3 Step -1
        If CDate(ActiveSheet.Range("H" & x).Value) = Date And Cells(x, 3) = "1" Then
            strProjects(count) = Cells(x, "E") ' error happens at this line?
            count = 1 + count
        End If
    Next x

The error says the cursor cannot be identified, or Run time error 9? I do not know what I am doing wrong.

Re: Is Date Past X Weeks From Current Date

How would I do it in VBA? I tried the following, but I am getting a mismatch error

(Date - CDate(ActiveSheet.Range("F" & x).Value)) < 28

Re: Sum Of The Parts Equals The Whole

vba and pseudo code is basically advanced alegebra,

SHG, thank you, i realize I should have put either 1 or 2 for possible solutions, instead of 0, it is still doing the calculations.

Re: Sum Of The Parts Equals The Whole

i think it has to be done with vba, but i dont know the formula to do it.

Is there a function, or how would I write a vba to figure out the following.

I have 86 items all with a different price, which come to a total of $348,359.33, is there a way to figure out which combination of the 86 entries will give me a total of $57,673.36

How would I write an if statement that checks to see if the date entered is two weeks past?

Re: Prompt Upon Cell Selection

Thank that is the function I needed

Re: Centre An Inserted Image

thank you

I am having some troubles inserting an image than centering it within the cell.
When I do this:

Set rng = Target.Cells
            With pic
                .Height = rng.Height 
                .Width = rng.Width 
                .Left = rng.Left 
                .Top = rng.Top 
            End With

The picture fits within the borders of the cell, but when I do this:

Set rng = Target.Cells
            With pic
                .Height = rng.Height /2
                .Width = rng.Width /2
                .Left = rng.Left /2
                .Top = rng.Top /2
            End With

The picture is the right size but not within the cell.

Hello

Which function would I use if I wanted a macro to run upon the user either clicking on a cell within a certain column, or tabbing to a certain column.

Right now I am using

Private Sub Worksheet_Change(ByVal Target As Range)
End Sub

But it doesn't take effect until after the user has inputted something into the cell.

Re: Hyper Linking

That works perfect thank you so much.[hr]*[/hr] Auto Merged Post;[dl]*[/dl]Thanks that works perfectly.[hr]*[/hr] Auto Merged Post;[dl]*[/dl]Thanks that works perfectly.

Hello,

I have attached an example spreadsheet to describe what I am working with. I was wondering if there is an easy way to have it so that everything in Column A is hyper linked to the ship bill file. This list can exceed over 1000 and to hyper link each line is too much, is there an automatic way to do this?

For example. I would like the value '00001 in cell A2, to be hyper linked to the file "Shipping Bill - 00001" So that I can just click on the link and open up the file.

The shipping bill files are in the same folder as the example spreadsheet.

Re: Print Outside File From Spreadsheet

Matthew,

Ok so now Adobe open but it says it cannot find the file, in column H it displays,
G:\Contract Folders\30101 - Toyota, Paint\30101 - Detail Drawings\a106188.pdf

I've checked and there is a file there named this. Why do you think I am getting this error, is it becuase there are spaces in the folders name?

Re: Print Outside File From Spreadsheet

Matt,

Thank you for your help, but I'm getting the error "Method 'Run' of Object 'IWshShell3' Failed.

this is the code I've used.

Sub PrintingTest()
    Dim sFileName As String
    Dim sPrintLocation As Integer
     
    For sPrintLocation = 1 To 85
         
        sFileName = Cells(1, 8)
         'Prints one copy.
        PrintPDF2 sFileName, 1
         
         'Prints three copies
         'PrintPDF2 sFileName, 3
         MsgBox "Data has been sent. " & vbLf & _
        "Please close the instance of Acrobat Reader after printing."
         
    Next sPrintLocation
End Sub


Sub PrintPDF2(ByVal FileName As String, Optional Copies As Long = 1)
'Your printer name from the Print Test Page
    Const PrinterName As String = """Xerox WorkCentre 5655 PS"""


'Your DriverName from the Print Test Page
    Const DriverName As String = """PSCRIPT5.DLL"""


'Your PortName from the Print Test Page
    Const PortName As String = """X_192.168.1.5"""
    Dim cnt As Long
    Set myShell = CreateObject("WScript.Shell")
    For cnt = 1 To Copies
         
         'Change the application name here, if required.
        myShell.Run ("Acrobat.exe /t " & FileName & " " & PrinterName & " " & DriverName & " " & PortName)
    Next
End Sub

And attached is a copy of my print test page, did I make a mistake in transferring the information.