Posts by mhabib

Re: Referencing an External Workbook

Or This:

ActiveCell.Offset(0, 2).Formula = "='" & myPath & "[RemoteFile.xls]Weekly'!$I$37"

m

PS: You'll need to ensure that mypath ends with a backward slash.

Re: Take data from one sheet and paste it in another

elopez,

I'm the one who should be thanking you. I've incorporated the same code into my trip planner. At first I was puzzled that you were'nt using MS Outlook to schedule your meetings. But as I saw in my own case, some things are best left with Excel.

Regards,

m

Re: Editing Custom Views

Thanks Derk.

m

Re: Deleting an Add-In from List

Thanks royUK. That works fine.

m

Hi,

I need to add a footer in one of my custom views. However, I see no option available for editing custom views. I tried the Add command, but it requires me to retype the name of the custom view - something I'd rather not do. Any suggestions?

Thanks.

m

Hi,

Is there a command to delete an add-in from the Add-ins List?

Reason: I had earlier created an add-in that I'm no longer using, and I would like to remove its name from the Add-ins list.

Thanks.

m

Re: Macro to reconcile monthly vouchers

Can you post up and example? Remember to include the actual data layout, along with details of the desired result.

Regards,

m

Re: Take data from one sheet and paste it in another

Hi elopez,

The code below seems to work for me. You can incorporate it into your workbook by pasting it to Sheet1's code window

Private Sub Worksheet_Calculate()
    Dim Sht1 As Worksheet
    Dim Sht2 As Worksheet
    Dim rngCal As Range
    Dim rngAppt As Range
    Dim i As Integer
    Dim msgTxt As String
    
    Set Sht1 = Sheets("Sheet1")
    Set Sht2 = Sheets("Sheet2")
    
    Sht1 _
        .Range("B15:AA20,B22:AA27,B29:AA34,B36:AA41,B43:AA48,B50:AA55") _
        .ClearContents
    
    For Each rngCal In _
        Sht1.Range("B14:AA14,B21:AA21,B28:AA28,B35:AA35,B42:AA42,B49:AA49")
        
        If rngCal <> "" Then
            
            i = 1
            
            For Each rngAppt In Sht2.Range("E2", Sht2.Range("E65536").End(xlUp))
                If rngAppt = rngCal Then
                    If i > 6 Then
                        msgTxt = "AARG - Too Many Appointments on " & _
                            Format(rngCal, "dd-mmm-yyyy") & vbCr & vbCr & _
                            "macro terminated"
                        MsgBox msgTxt
                        Exit Sub
                    End If
                    rngCal.Offset(i, 0).Value = _
                        Format(rngAppt.Offset(0, -2).Value, "hh:mm") & " ** " & _
                        rngAppt.Offset(0, -4).Value & " ** " & _
                        rngAppt.Offset(0, -3).Value
                        
                    i = i + 1
                End If
            Next rngAppt
        End If
    Next rngCal
End Sub

HTH

m

Re: VBA Code to Change File Extension

Norie - You Rock !

Thanks a mil.

m

Re: VBA Code to Change File Extension

Thanks Norie.

The Name statement worked great - but only once. Apparently, it won't overwrite a file if it already exists. In other words, if closing.txt already exists, and I run this command:

Name "C:\Closing.Lis" As "C:\Closing.Txt"

Then I get the following error:

Quote

Run-time error '58':

File already exists

Any thoughts?

m

Hi,

I'm looking for code to rename a file C:\Closing.Lis to C:\Closing.Txt from an Access module.

Thanks

m

[Solved]Re: Adding Menu Item to Worksheet Menu Bar

Thanks for the help guys!

dacat1997: You Rock! Thanks to you, I have a fighting chance to deliver my project on time.

Norie: My problem's solved. But here's the code that was generated when I recorded the macro to add a new menu to the Worksheet Menu Bar. FYI, it creates a new menu item, but does not take up the caption entry made during the recording process.

Sub AddMenuItem()
'
' AddMenuItem Macro
' Macro recorded 26/5/2005 by M A Habib
'


'
    Application.CommandBars("Worksheet Menu Bar").Controls.Add Type:= _
        msoControlPopup, Before:=11
End Sub

A million thanks once again.

m

Re: Convert schedule into database

Hi tqv1969,

I tried the following code in your workbook and it seemed to work fine.

Sub ScheduleToDatabase()
    Dim rngcell As Range
    Sheets("Database").Activate
    Range("A4", Range("A4").SpecialCells(xlCellTypeLastCell)) _
        .ClearContents
    
    Sheets("Schedule").Activate
    For Each rngcell In Range _
        ("B4", Range("B4").SpecialCells(xlCellTypeLastCell))
        
        If Not IsEmpty(rngcell) Then
            With Sheets("Database").Range("A65536").End(xlUp).Offset(1, 0)
                .Offset(0, 0).Value = Cells(rngcell.Row, 1).Value
                .Offset(0, 1).Value = Cells(3, rngcell.Column).Value
                .Offset(0, 2).Value = rngcell.Value
            End With
        End If
    Next
End Sub

HTH

m

Re: Adding Menu Item to Worksheet Menu Bar

Thanks for the suggestion, Norie.

I've already tried the record-and-run approach. Unfortunately, I get an illegal operation error when I try to run the recorded code.

I have the option of creating a custom toolbar and attaching it to the workbook. However, I'd like to generate the commandbar programmatically so that the user cannot simply delete/alter its settings.

m

Hi,

I'd like to Add a new menu item to the Worksheet Menu Bar. The caption on the new menu should be Cost Statement. Within the new menu item, I'd like to add the following buttons.

First Button:
Caption: Reset Report
Macro: ResetAll

Second Button:
Caption: Locate Missing IDs
Macro: UnmatchedListing

The new menu item needs to be added in when the ThisWorkbook opens, and the worksheet menubar is to be reset each time ThisWorkbook closes.

Any help would be highly appreciated.

Thanks.

m

PS: ThisWorkbook is a normal excel file (xls) - Not an Add-in

Re: Copying data from the Internet

No suggestions at this point.

regards,

m

Re: Code for including a Reference: DAO 3.6

Hi Guys!

Thanks for the replies.

Tanis:
I checked out the kb link, and I guess you're right about not being able to call the DAO reference programmatically.

RowanS:
Thanks for your suggestion. I'll give it a try.

Regards,

m

Re: Copying data from the Internet

Hi Richard,

Data Parsing refers to converting running-text data into columns (or Text-to-Columns in Excel-Speak). If you're simply copy/pasting to Excel, then parsing is not an issue. However, using the PasteSpecial > Text command would pose a parsing problem.

m

Hi,

I need code to programmatically include Microsoft DAO 3.6 Object Library at run-time rather than having the user do it in VBE by clicking Tools > Reference.

Any help will be highly appreciated.

m

Re: VBA Historical Price Data Loop

Hi Johan,

Just add the following code right after the sub statement (or anywyere near the top of the procedure).

On Error Resume Next

This will omit STDEV computations for one-liner securities.

HTH

m