Posts by ashu1990

ashu1990 · Feb 26th 2015

Re: Using VBA can we check the FONT format all the files in a Specific Folder

replace this

Code

ThisWorkbook.Worksheets("Font Checker").ClearContents

with this

Code

ThisWorkbook.Worksheets("Font Checker").usedrange.ClearContents

ashu1990 · Feb 26th 2015

Re: (newbie) Count a range of labels, split in half and highlight the top half

attach a workbook with the input data and the out put data as required..

ashu1990 · Feb 26th 2015

Re: Macro needed to enable and disable an Active X ComboBox

tried your code it worked for me....

try giving the sheet name before the combobox name.. eg:- sheet1.combobox1.enabled = true

ashu1990 · Feb 26th 2015

Re: Using VBA can we check the FONT format all the files in a Specific Folder

create a sheet named Font Checker as i have removed the code to create a sheet and try this new code

Code

Option Explicit 
Sub test() 
    Dim wbk As Workbook, path As String, i As Long, fname As String, cell As Variant,font_cri  as string
    path = ActiveSheet.Range("B3").Value '<--- Path of the folder
    font_cri = ActiveSheet.Range("C3").Value 
     
    ThisWorkbook.Worksheets("Font Checker").clearcontents


    fname = Dir(path & "\", vbNormal) 
    Do While fname <> "" 
If Mid(fname, InStr(fname, "."), 4) <> ".xls" Then Goto SKP: 
        Set wbk = Workbooks.Open(path & "\" & fname) 
        For i = 1 To wbk.Worksheets.Count 
             
            For Each cell In wbk.Worksheets(i).UsedRange 
                 
                If cell.Font.Name <> font_cri Then 
                     
                    With ThisWorkbook.Worksheets("Font Checker") 
                         
                        .Range("A" & Range("A:A").Rows.Count).End(xlUp).Offset(1, 0).Value = wbk.FullName 
                        .Range("B" & Range("B:B").Rows.Count).End(xlUp).Offset(1, 0).Value = wbk.Worksheets(i).Name 
                        .Range("C" & Range("C:C").Rows.Count).End(xlUp).Offset(1, 0).Value = cell.Address(0, 0) & "-" & cell.Value & "- Arial Font Is Not Matching" 
                         
                    End With 
                     
                End If 
                 
            Next cell 
             
        Next i 
         
        wbk.Close False 
SKP: 
        fname = Dir() 
    Loop 
    ThisWorkbook.Worksheets("Font Checker").Range("A1").Value = "File Name" 
    ThisWorkbook.Worksheets("Font Checker").Range("B1").Value = "Sheet Name" 
    ThisWorkbook.Worksheets("Font Checker").Range("C1").Value = "cell Value and address" 
End Sub

Display More

ashu1990 · Feb 26th 2015

Re: VBA to click checkbox in Internet Explorer

hey olsonj dont give up easily see if the checkbox is inside the frame how ever you can loop through all the elements in the web page and see if you finds the correct ID for the check box

Code

Set ElementCol = ie.Document.getElementsByTagName("input")


For Each btnInput In ElementCol


msgbox btninput.name
msgbox btninput.id
msgbox btninput.innerhtml




Next btnInput

Display More

ashu1990 · Feb 26th 2015

Re: Using VBA can we check the FONT format all the files in a Specific Folder

here's the altered code

Code

Option Explicit
Sub test()
    Dim wbk As Workbook, path As String, i As Long, fname As String, cell As Variant
    path = ActiveSheet.Range("B3").Value '<--- Path of the folder
    font_cri = ActiveSheet.Range("C3").Value
    Module1.delete_sheets ("Font Checker")
    
    ThisWorkbook.Sheets.Add after:=ActiveSheet: ActiveSheet.Name = "Font Checker"
    fname = Dir(path & "\", vbNormal)
    Do While fname <> ""
If Mid(fname, InStr(fname, "."), 4) <> ".xls" Then GoTo SKP:
        Set wbk = Workbooks.Open(path & "\" & fname)
        For i = 1 To wbk.Worksheets.Count
             
            For Each cell In wbk.Worksheets(i).UsedRange
                 
                If cell.Font.Name <> font_cri Then
                     
                    With ThisWorkbook.Worksheets("Font Checker")
                         
                        .Range("A" & Range("A:A").Rows.Count).End(xlUp).Offset(1, 0).Value = wbk.FullName
                        .Range("B" & Range("B:B").Rows.Count).End(xlUp).Offset(1, 0).Value = wbk.Worksheets(i).Name
                        .Range("C" & Range("C:C").Rows.Count).End(xlUp).Offset(1, 0).Value = cell.Address(0, 0) & "-" & cell.Value & "- Arial Font Is Not Matching"
                         
                    End With
                     
                End If
                 
            Next cell
             
        Next i
         
        wbk.Close False
SKP:
        fname = Dir()
    Loop
    ThisWorkbook.Worksheets("Font Checker").Range("A1").Value = "File Name"
    ThisWorkbook.Worksheets("Font Checker").Range("B1").Value = "Sheet Name"
    ThisWorkbook.Worksheets("Font Checker").Range("C1").Value = "cell Value and address"
End Sub
Public Sub delete_sheets(ByVal shname As String)
On Error Resume Next
Application.DisplayAlerts = False
ThisWorkbook.Worksheets(shname).Delete
Application.DisplayAlerts = True
End Sub

Display More

ashu1990 · Feb 25th 2015

Re: VBA to click checkbox in Internet Explorer

are you sure that the checkbox name you are using is the right checkbox name or id..

any ways try this..

Code

ie.document.forms(0).all("BATCH-EXPORT").item(0).checked = true


'or 


ie.document.forms(0).all("BATCH-EXPORT").item(0).click


'or 


ie.document.forms(0).all("BATCH-EXPORT").click

Display More

its difficult to guess with out accessing the web page...

ashu1990 · Feb 25th 2015

Re: Using VBA can we check the FONT format all the files in a Specific Folder

hi bindu,

welcome to the forum... as you should know that the task which you want usses loops and will check each cell in the file on every sheet so be patient while running the macro as its gona take a lot of time depending on your data size.....

Code

Option Explicit
Sub test()
Dim wbk As Workbook, path As String, i As Long, fname As String, cell As Variant
path = ""  '<--- Path of the folder
ThisWorkbook.Sheets.Add after:=ActiveSheet: ActiveSheet.Name = "Font Checker"
fname = Dir(path & "\", vbNormal)
Do While fname <> ""
If Mid(fname, InStr(fname, "."), 4) <> ".xls" Then GoTo SKP:
Set wbk = Workbooks.Open(path & "\" & fname)
For i = 1 To wbk.Worksheets.Count


    For Each cell In wbk.Worksheets(i).UsedRange
    
        If cell.Font.Name <> "Arial" Then
        
            With ThisWorkbook.Worksheets("Font Checker")
            
            .Range("A" & Range("A:A").Rows.Count).End(xlUp).Offset(1, 0).Value = wbk.FullName
            .Range("B" & Range("B:B").Rows.Count).End(xlUp).Offset(1, 0).Value = wbk.Worksheets(i).Name
            .Range("C" & Range("C:C").Rows.Count).End(xlUp).Offset(1, 0).Value = cell.Address & "-" & cell.Value & "- Arial Font Is Not Matching"
            
            End With
        
        End If
    
    Next cell
    
Next i
    
wbk.Close False
SKP:
fname = Dir()
Loop
ThisWorkbook.Worksheets("Font Checker").Range("A1").Value = "File Name"
ThisWorkbook.Worksheets("Font Checker").Range("B1").Value = "Sheet Name"
ThisWorkbook.Worksheets("Font Checker").Range("C1").Value = "cell Value and address"
End Sub

Display More

ashu1990 · Feb 23rd 2015

Re: Prevent outlook popup.

it cant be done by vba you have to allow it manually or use a third party addin into the outlook to bypass the popup or use your SMTP server to send the mail..

here are some usefull links

for addin http://www.experts-exchange.co…e/Outlook/Q_26601688.html

for sending Mail through Server side http://www.rondebruin.nl/win/s1/cdo.htm

ashu1990 · Feb 23rd 2015

Re: Activate file name based on cell value date in a sheet vba

.activate should be outside the )

Code

Windows ("CNAV" & " " & "IACB" & " " & Format(Sheets("Main").Range("C6").Value, "yyyymmdd")) .Activate

ashu1990 · Feb 23rd 2015

Re: Activate file name based on cell value date in a sheet vba

you have not given space in the file name

Code

Windows ("CNAV" & " " & "IACB" & " " & Format(Sheets("Main").Range("C6").Value, "yyyymmdd").Activate)

ashu1990 · Feb 23rd 2015

Re: Activate file name based on cell value date in a sheet vba

whats the actual file name...

ashu1990 · Feb 23rd 2015

Re: Pass complete path

you have put an extra "\"

Code

path1 = "C:\Files\Low\" & BIN_nr & "_" & Company_name & "\" & "Mails & " \ ""

ashu1990 · Feb 23rd 2015

Re: Activate file name based on cell value date in a sheet vba

how about

Code

workbooks(("CNAV" & "" & "IACB" & "" & Format(Sheets("Main").Range("C6").Value, "yyyymmdd").activate

ashu1990 · Feb 20th 2015

Re: Change font of the last word in a cell if its in brackets

thanks mate both the links are good will try to work more with regex with every task

ashu1990 · Feb 20th 2015

Re: Change font of the last word in a cell if its in brackets

agreed with apo Jindon your codes always use regex can you just clear some logic behind it..

ashu1990 · Feb 20th 2015

Re: Change font of the last word in a cell if its in brackets

put this in worksheet code panel and change the column name as desired

Code

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim sh
For Each sh In Range("A:A")
If Len(sh) = 0 Then GoTo TT:
If VBA.Mid(sh, Len(sh), 1) = ")" Then
With sh.Characters(Start:=InStr(sh, "("), Length:=Len(sh)).Font
        .Name = "Calibri"
        .FontStyle = "Regular"
        .Size = 11
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
        .ThemeFont = xlThemeFontMinor
    End With
 
End If
TT:
Next sh
 
End Sub

Display More

ashu1990 · Feb 16th 2015

Re: Make HTML table lines visible via vba

i didn't get it i ran the code and worked perfect for me anyways here's a useful link i found might help you http://www.pindari.com/rtf3.html

ashu1990 · Feb 13th 2015

Re: Help !!!

x-stealth welcome to the forum, please do not use words like help or save me as it will not Grapse attention of the people who intend to help you...

always use title which is asociated with your question this is what you agreed upon joining the forum...

ashu1990 · Feb 13th 2015

Re: Block cell to change after a certain time.

I hope tom you got your answer..:smile: