Posts by ashu1990

Re: Auto lock selected cells from different sheets in a workbook after saving

Hi pugoot6,

Press ALT+ F11 and double click on Thisworkbook
paste the code in Thisworkbook editor page

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim ran As Range
Set ran = Range("F2:H8")
    ran.Locked = False
    ran.FormulaHidden = False
ActiveSheet.Protect "Your Password"
 


End Sub

give the cells range into the Range("F2:H8") part
Hope this helps

Re: submeny enable

be clear about what are you asking unless we dont understand the question we cant help you

Re: VBA to add email of the sender to be copied

the code

Title = Range("b5")

shows the title of the .PDF you can get the title or change the title from there and for the mail part you can add emails id's like this ""[email protected]"[/email];[email protected]".

hope this helps.

Re: Vba code to copy data from one worksheet to another

???? what exactly do you want

Re: Copy selected cells from one workbook and past to another workbook

Hi chris,

i have made this code long time ago for 2 sheets hope this helps

Sub test()
Dim LR As Long, i As Long, cls
cls = Array("A1", "C4", "F7", "G8", "H2")
With Sheets("Sheet2")
    LR = WorksheetFunction.Max(6, .Range("A" & Rows.Count).End(xlUp).Row + 1)
    For i = LBound(cls) To UBound(cls)
     
    .Range(Left(Range(cls(i)).Address(1, 0), InStr(Range(cls(i)).Address(1, 0), "$") - 1) & 65536).End(xlUp).Offset(1, 0).Value = Sheets("Sheet1").Range(cls(i)).Value
        
    Next i
End With
End Sub

Re: Difference between two times, and adding more time to the result

replace with this

Range("d1").Value = Format(Range("c1") + TimeValue("00:" & LB & ":00"), "HH:MM:SS")

Re: Copy Mail content and paste it in Excel using VBA

hi jsc,

The code worked for me it copied the whole mail item into the cell A1

any ways i tried this my self see if the code helps

Sub GetFromInbox()
     
    Dim olApp As Outlook.Application
    Dim olNs As Outlook.Namespace
    Dim olFldr As Outlook.MAPIFolder
    Dim olItms As Outlook.Items
    Dim olMail As Variant
    Dim i As Long
     
    Set olApp = New Outlook.Application
    Set olNs = olApp.GetNamespace("MAPI")
    Set olFldr = olNs.GetDefaultFolder(olFolderInbox)
    Set olItms = olFldr.Items
     
         
    i = 1
     
    For Each olMail In olItms
        
        Sheets("Sheet1").Cells(1, j + 1).Value = olMail.Body
        
        j = j + 1
        
    Next olMail
     
    Set olFldr = Nothing
    Set olNs = Nothing
    Set olApp = Nothing
     
End Sub

Re: Current year in listbox

try this

Sub test()
Dim i As Long
 i = 1
Do
If Range("A" & i).Value = "" Then Exit Do
If Format(Range("A" & i).Value, "YYYY") = Format(Now(), "YYYY") Then
Sheet1.ListBox1.AddItem Range("A" & i).Value
End If
i = i + 1
Loop
End Sub

Re: Copy Mail content and paste it in Excel using VBA

replace

Range("B" & i + 1).Value = str1(i)

with this

Range("B" & i + 1).Value = left(str1(i) ,6)

Re: Copy Mail content and paste it in Excel using VBA

try this

Sub extract_data_frm_cell()
Dim str1 As Variant, i As Long
str1 = Range("A1").Value


str1 = Split(str1, Chr(10))
For i = 0 To UBound(str1)
Range("B" & i + 1).Value = str1(i)
Next i
End Sub

this will extract the data from cell A1 and put the result into column B

Re: import txt. file to excel with vba

try this

Public num As String, path As String
Public Sub get_text_file()
path = Application.GetOpenFilename
    With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & path, Destination:=Range("$A$1"))
        .Name = "loop docket number"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    
    Range("IV1").End(xlToLeft).Offset(0, 1).Select
    
    Do While Range("A" & ActiveCell.Row).Value <> ""
    
  Module1.get_number ActiveCell.Offset(0, -1).Value
  ActiveCell.Value = num
num = ""
ActiveCell.Offset(1, 0).Select
    Loop
    
End Sub
Public Sub get_number(ByVal str1 As String)
Dim i As Long
For i = 1 To Len(str1)
If Mid(str1, i, 1) <= 9 And Mid(str1, i, 1) >= 0 Then
num = num & Mid(str1, i, 1)
End If
Next i
End Sub

Re: VBA code sends email even if there conditions are not met

good work ramesh..

Re: formula to skip a text and get the large number into the range

Jindon thanks a lot you rocked.....
thats exact what i needed
have a nice day

Re: formula to skip a text and get the large number into the range

ramesh got it right thats exact what i want i want the result as

80,50 & 40 which will not take any values from I which is in column A

Hello guru's,

i am not sure if it can be done but any thing is possible for the guru's

Column A Column B
[TABLE="width: 96"]

[TD="width: 64, bgcolor: transparent"]I[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]10[/TD]

[TD="bgcolor: transparent"]I[/TD]
[TD="bgcolor: transparent, align: right"]90[/TD]

[TD="bgcolor: transparent"]A[/TD]
[TD="bgcolor: transparent, align: right"]30[/TD]

[TD="bgcolor: transparent"]B[/TD]
[TD="bgcolor: transparent, align: right"]40[/TD]

[TD="bgcolor: transparent"]C[/TD]
[TD="bgcolor: transparent, align: right"]50[/TD]

[TD="bgcolor: transparent"]I[/TD]
[TD="bgcolor: transparent, align: right"]60[/TD]

[TD="bgcolor: transparent"]D[/TD]
[TD="bgcolor: transparent, align: right"]80[/TD]

[/TABLE]

i want the A,B,C,D large numbers but excluding the I into the range

the formula so far i have tried is "=IF(A1="I",OFFSET(A1,1,0),LARGE($B$1:$B$7,1))"

i am really clueless can any one help me?

Re: VBA to convert IE to firefox

this link might help

http://www.mrexcel.com/forum/e…cations-web-browsing.html

Re: Set expiry date to macro

Hi nikimitsy,

just save the start date of the macro into a hidden cell where no one can see and then ask the macro to check Regularly if the date expires.

this will do the work

Re: Automatically copy last month's sheet and add it to a new sheet

This will do your work the way you want

Sub Add_sheet()
Dim lstmonth
lstmonth = Format(DateSerial(Year(Now()), Month(Now()) - 1, 1), "MMM")
Sheets.Add after:=Sheets(lstmonth): ActiveSheet.Name = Format(DateSerial(Year(Now()), Month(Now()), 1), "MMM")
Worksheets(lstmonth).UsedRange.Copy
Worksheets(Format(DateSerial(Year(Now()), Month(Now()), 1), "MMM")).Range("A1").PasteSpecial (xlAll)
Application.CutCopyMode = False
Range("A1").Select
End Sub

Re: VBA Sendkeys fails to send keystroke inside VMware Internet explorer

can any one with vmware experience can guide me

Hi All,

I am trying to send keystroke using send keys , i am sending cntrl + shift + S this combination help's to open the save as dialoge box inside internet explorer.

But i am not able to use this sendkeys inside a VMware the code is running fine outside the vmware.

when i run the code into the VMware nothing happens but the Num lock light goes off ! <--- whats the reason behind this?

i am trying to save an .PDF from an internet explorer from a Bank website so the urldownload function doesnt work there as its .PDF path is not complete as per security purpose of the web site.

also tried to set the cursor into the position of the internet explorer Command FILE and simulated a left click so that i can use the saveas option, but the setcursorpos dosen't work eiter.

is there any other way to save a .pdf from a web site.