Posts by ashu1990

see if this helps

Sub test()


For Each i In ThisWorkbook.Sheets


With Worksheets(i.Name)
    With .PageSetup
        .PrintArea = "$C$2:$L$60"
        .Orientation = xlPortrait
        .Zoom = 50
        .FitToPagesWide = 1
        .FitToPagesTall = False
        .LeftMargin = Application.InchesToPoints(0)
        .RightMargin = Application.InchesToPoints(0)
        .TopMargin = Application.InchesToPoints(0)
        .BottomMargin = Application.InchesToPoints(0)
        .HeaderMargin = Application.InchesToPoints(0)
        .FooterMargin = Application.InchesToPoints(0)
    End With
    .PrintOut
End With




Next i


End Sub

See if this helps.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If UCase(ThisWorkbook.Sheets("CSG 130").Range("I$64").Value2) <> "PASS" Then
MsgBox "Analysis Outcome is Failed!!!!"
Cancel = True
End If
End Sub

a sample workbook and details would help.

[SIZE=14px]PictureSizeMode Property
Another property of the image control is the PictureSizeMode.

If the property is set to the default value 0-frmPictureSizeModeClip the control size can be changed without the picture size being modified. So you can see only part of the picture or there can be a background behind it in a clolor color you can change at will.

If the property is set to the 1-frmPictureSizeModeStretch the picture is resized as the control is. The image fills the control.

If the property is set to the 3-frmPictureSizeModeZoom the picture is resized as the control is but the picture and background are present.

press like if it worked!!![/SIZE]

an excel workbook with Dummy data would help.

ok here is the problem you are having old file name when it was opened and after you saved the workbook with its new name you did not again loaded the variable with the new file name.

Filename = ActiveWorkbook.Name
ActiveWorkbook.SaveAs Filename:=FolderName & "\" & Replace(myFile, ".txt", ".xlsx"), FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

change this to this

ActiveWorkbook.SaveAs Filename:=FolderName & "\" & Replace(myFile, ".txt", ".xlsx"), FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
Filename = ActiveWorkbook.Name

Hey Jonny,

i have PM you please check,

you have to give the file a path hence its saving it in a unknown location, below code will save the file where you have saved your macro file.

ActiveWorkbook.SaveAs thisworkbook.path & "\" & Replace(Range("L6").Value & "-" & Range("N2").Value & "-" & Range("A6").Value, ".", "") & ".xlsm", 52

you have "" into your code remove it..

Application.Workbooks("filename").Activate ' <-- will not give you file name
Application.Workbooks(filename).Activate   '<-- this will

assuming you have timer in Cell B2 running the below code will work

Function BeepMe() As String
tim = Range("B2").Value


Do Until Range("B2").Value = (tim + TimeValue("00:00:10"))
DoEvents
Loop
Beep
BeepMe = ""




End Function

Hi Z,

you are asking to create a whole automation process hence you will not get help most likely here , try the hire help section.

in context to what jonathan wanted to say, see below code, also please try to play with your code this is the way you will learn new things.

Sub tabname()
' Names each newley created tab with the scps name
Application.ScreenUpdating = False
Worksheets("PivotTables(1)").Activate


Dim ws As Worksheet
For Each ws In Worksheets
On Error Resume Next
If Len(ws.Range("P2")) > 0 And ws.Name Like Replace(ws.Range("P2").Value, "/", "-") Then
ws.Name = Replace(ws.Range("P2").Value, "/", "-")
End If
On Error GoTo 0
If ws.Name <> Replace(ws.Range("P2").Value, "/", "-") Then
MsgBox ws.Name & " Was Not renamed, the suggested name was invalid"
End If
Next
End Sub

i can take this up let me know if you still looking for this

this will do the trick removed . and replaced it with space as it hampers saving excel file format

ActiveWorkbook.SaveAs Replace(Range("L6").Value & "-" & Range("N2").Value & "-" & Range("A6").Value, ".", "") & ".xlsm", 52

Hey Home,

I had this problem too and what you need to do is change the setting of your data connections. By default, Excel will "Enable background refresh". This must be turned off and it will force the macro to complete the refresh task before it moves on. If using Excel 2007, bring up the "Connection Properties" and uncheck the box that enables the background refresh. You can get there by clicking on the table that gets refreshed and where you would click to actually refresh the data you should see an arrow for more options.

Re: take value from 2 multi listboxes and search row for criteri

you have to give the value of list box into the lst1 and listbox 2 into lst2...

lst1 = Me.listbox1.Value lst2 = Me.listbox2.Value <---- see that you are passing the right values here i didnt checked it with list box .

if the null value passes into this string hopefully it will not run.

Re: IE Automation: Click button (Object invoked has disconnected from clients)

Didnt see i am posting to an old tread sorry guys :0ops:

any ways i think it will help some one for sure...

Re: IE Automation: Click button (Object invoked has disconnected from clients)

if you still did not find a solution try this after you open the url in ie

Sub test()
Dim SWs As Object, vIE As Object
Set SWs = CreateObject("Shell.Application")
For Each vIE In SWs.Windows
Debug.Print vIE.LocationURL
If vIE.LocationURL = "YOUR URL" Then
Set IE = vIE
Exit For
End If
Next
End Sub

this will again set the instance of the ie into the variable, it happens coz you loose the instance of the internet explorer

Re: Expand Outlook Distribution List using Excel VBA

try this

Const olFolderContacts = 10
 
Sub DistList()
    Dim objApp As Object, objNS As Object
    Dim objFolder As Object, objDist As Object
    Dim objAddrEntry As Object
    Dim MyList As String
    Dim ws As Worksheet
     
    Set ws = ActiveWorkbook.Worksheets.Add
     
    MyList = "mylist" ' change your list name here and it should be in your contacts
    Set objApp = CreateObject("Outlook.Application")
    Set objNS = objApp.GetNamespace("MAPI")
    Set objFolder = objNS.GetDefaultFolder(olFolderContacts)
    On Error Resume Next
    Set objDist = objFolder.Items(MyList)
    On Error GoTo 0
    If objDist Is Nothing Then
        MsgBox "Distribution List doesn't exist", vbCritical
        GoTo fastexit
    End If
     
    For i = 1 To objDist.MemberCount
        Set objAddrEntry = objDist.GetMember(i).AddressEntry
        Cells(i, 1) = objAddrEntry.Name
        Cells(i, 2) = objAddrEntry.Address
    Next
fastexit:
    Set objFolder = Nothing
    Set objApp = Nothing
End Sub

Re: Modify my code to copy ranges to email

this codes copy charts into email body, hope you will be able to ammend it into yours for your better learning.

Sub CopyAndPasteToMailBody()
    Set mailApp = CreateObject("Outlook.Application")
    Set mail = mailApp.CreateItem(olMailItem)
    mail.Display
    Set wEditor = mailApp.ActiveInspector.wordEditor
    ActiveChart.ChartArea.Copy
    wEditor.Application.Selection.Paste
End Sub