Posts by amritduttgautam

Re: Automatic Dynamic Sort

As per me the best place to put ontime function is "Workbook_Open" event

Re: Automatic Dynamic Sort

Hi Troller,

If you want the sheet to be sorted after specified intervals then use ontime function.You can refer to this page
run-macro-on-time.

Re: Send Email Alert Color Coded Due Dates

Take a look at This thread may be it will help you

Re: Copy Range Of Cells Into Email

Hi,

I am not sure do you want the range name or the contents of the range to be displayed on message box

Re: Save A File As A Text (tab Delim) File Type

I think that is just a icon because you are saving it from excel.

If you right click on the file and select open with>choose program. a window will appear select "Notepad" application. and it will open in notepad and not in Excel.

You can also select the Check box on the window, that comes after Open with>Choose program, that says "always use the selected program to open this kind of file"

Re: Save A File As A Text (tab Delim) File Type

Hi,

What i can understand is that you want a CSV file not tab delimited file
I tried it on my system and in Save As dialog box there is a option to save the file as csv if scroll a bit down in "Save as Type: " list.
I am not sure if this has any thing to do with version . I am using Excel 2002

Re: End Macro With Inappropriate User Input Or Exit Button Pushed

Can you please post the code that you have.

Re: Option Button Creates Text

Hi

you can also use the radio buttons on forms toolbar and this dose not require any macro. please the sheet attached.

I have used two radio buttons and tied them to cell A1 on the Sheet then used if formula in cell F23 to get the appropriate value in the cell.

testing Hyperlink www.google.com

Quote

Amrit Dutt Gautam

Amrit Dutt Gautam

MODERATOR NOTICE: This topic has also been posted on other sites and may already have an answer elsewhere. Please take this into consideration when answering this question

Amrit Dutt Gautam

[RDV]Amrit Dutt Gautam[/RDV]

[VLOOKUP]Amrit Dutt Gautam[/VLOOKUP]

[PT]Amrit Dutt Gautam[/PT]

just checking weather formatting works in thread title as well :cheers:[hr]*[/hr] Auto Merged Post;[dl]*[/dl]no it dosen't :smile:

Re: Send Active Workbook To More Than One Recipient

If the mail application is Lotus then you can alro refer to this thread

Re: Check Column Of Dates Against System Date And Send E-mail If Match

Hi,

You did not specify the Notes application however if you use lotus you can refffer to this thread sending male via lotus lotus notes.

to check weather the to match it with the system date you can define a function in the worksheet change event.

Re: Define Pivot Table Source As A Variable

Hi,

as per my understanding what you can do is that pick the name of the sheet in variable and then use that formula. Here is the simeple Example for refferencing the cell.

Sub Macro1()
    Dim SName As String
    Dim StrFormula
    SName = Sheets("APL").Range("D5")
    StrFormula = "=" + SName + "!D3"
    ActiveCell.Formula = StrFormula
    Range("D3").Select
End Sub

in the above code i have stored the sheet name in variable "SName" and then created the stirng "StrFormula" using this and then used this string as formula

Hope this is what you were trying to do

Re: Checking User Imput To Stop Run-time Error

Hi,

Try this code

Private Sub SortTest_Click()
    Dim s As String
    Dim r As Excel.Range
     
    Range("A2").Activate
    s = InputBox("Enter the number you wish to   find")
    If StrPtr(s) = 0 Then
         MsgBox "You must enter an existing part number!"
    Else
        Set r = Cells.Find(What:=s, After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)
         
       
        If r Is Nothing Then
        MsgBox "YOUR MESSAGE HERE"
        Else
        Range(Cells(r.Row, "A"), Cells(r.Row, "AH")).Copy
         Sheets("APL").Cells(r.Row, "A").Insert Shift:=xlDown
         
         
        Application.CutCopyMode = False
        Application.Goto Sheets("APL").Cells(r.Row, "H")
        Selection.Offset(-1, -5).ClearContents
        Selection.Offset(-1, 0).Select
        End If
    End If
End Sub

I have added a check to see weather it found the text or not by using

If r Is Nothing Then

Re: Case Sensitive Textbox Validation Against Numbers

Hi,

Thanks a lot to ala of you for your help finaly got what I needed .

.Pattern = "^[A-Z]+$"
        .IgnoreCase = True

Thanks again. :yourock:

Re: Case Sensitive Textbox Validation Against Numbers

Hi,

i tried both ways but it is not helping. when I enter value strting with number/Numeric then it gives me a message (which is OK) but when the Numeric is after text then it accepts it which is wrong.

What i want is that user should be able to enter chahracters between A to Z irrespective of Case (lower/upper) and nothing else no special characters, no spaces and no numerics.

Can this be done

Hi,

I have a user form to get some input from the user and want to make sure that in some textboxes user should be able enter only text i.e A to Z or a-z no numbers or special charecters.

i tried searching the threads and got this

Private Sub CommandButton1_Click() 
    Dim RegEx As Object 
    Dim Strng As String 
     
    Strng = CStr(Me.TextBox1.Value) 
    Set RegEx = CreateObject("vbscript.regexp") 
    With RegEx 
        .Pattern = "^[A-Z]{2}/\d+/\d{2}$" 
        If Not .test(Strng) Then  MsgBox "Invalid  Format: TextBox1" 
    End With 
    Set RegEx = Nothing 
End Sub

i modified it to

Sub Commandcheck()
    Dim RegEx As Object
    Dim Strng As String
     
    Strng = CStr(TextBox2.Value)
    Set RegEx = CreateObject("vbscript.regexp")
    With RegEx
        .Pattern = "[A-Z]" 
        If Not .test(Strng) Then MsgBox "Invalid  Format: TextBox1"
    End With
    Set RegEx = Nothing
End Sub

but this only accepts captial alphabets i.e A,B,C.... and not a,b,c...

please help

Re: Copy All Dat From Different Files Into One Sheet

Hi Tribestan,

I think you are receving this error message as there is no sheet named "sheet1" in your work book.If there is common name for the sheets in all the workbooks try replacing "Sheet1" by that

Re: Macro Slows System By Opening 140+ Windows

Hi,

Quote from mastermind82

Sorry, but I don't exactly understand where I would put those two lines. Could you please specify?

Try using them like this.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) 
    Application.EnableEvents = False
    Application.OnTime TimeValue("12:00:00"), "GetData" 
    Application.EnableEvents = True


End Sub

Re: Assigning Shortcut To A Macro

Hi Reafidy,

Thanks for Your help it is working great