Posts by Phil

I've been trying to search in every way possible on Ozgrid but I can't seem to find the answer...

I'm trying to have a popup with a dropdown that populates with names from the Outlook address book.

Additionally, I'd like the user to be able to select multiple names from this list that they'd like to send the attached workbook.

Again, I've tried to find this for I'm sure that this ground has been covered, but I cannot for whatever reason.

TIA

Phil

Re: Weather Lookup

Per your request...Attached is a sheet coded for Canada, both hitting the mapquest and weather sites.

Hope this helps.

Phil

Well I posted a mapquest and google search from excel, code last year. At that time I mentioned that I'd be doing one to lookup the weather as well.

Recently I was made aware that I never made good on that promise.

So here it is. It's super simple so don't be to up on it. Additionally, if your outside the US zip codes then you'll want to modify for your area.

See attached.

Phil

Re: Add a calendar to right click

I don't know if anyone would find this useful but I'm attaching a pop-up calendar that I built some time ago with input from many at this site. It does not use an ocx and works quite well.

You will note many various that it can be called or launched. However does not launch in any form of a right click menu.

Additionally, please note that I believe it did not work well with european date format (ie...day/month/year). I don't think that was ever resolved.

Re: Time Delay?

One way to accomplish this is to add a message box that tells the user of what just happened. While the message box is present the code is paused. Once the message box is dismissed by the user (by them clicking the OK button on the message box) the code will continue to run.

Insert the following after the line in the code that paste's the data.

msgbox ("Look, this is where the data has been pasted", vbok + vbinformation, "Just letting you know")

Hope this helps.

Best regards,

Phil

Re: Relative Directory Reference

I could but would rather figure the before mentioned method. In the end it's simple but effective.

Thanks for trying anyway.

Phil

I can’t seem to remember a simple relative directory reference.

This is what I’m trying to do… I have a cell where I used to hard code a directory path to a network directory. (i.e. H:\HMS\Master Files) Then in various Sub Routines I would reference that cell for the path.

What I’d like to do is put in that cell a relative reference to the same directory that the activeworkbook is located. I used this before but I can’t seem to remember or find an example of it. It was pretty elegant one could even use a similar method to look one directory up. I think it looked something like “\\”.

Any idea’s, it harkens back to old DOS/Basic programming but I believe it still works. I didn’t want to use VBA code i.e. “Thisworkbook.path” for I’d have to change it in so many places. I’d rather figure out the above.

Thanks,

Phil

Re: Global Workbook/sheet referance

Seems to have fallen in the list (bounce to top).

Gotta feeling it's pretty simple just drawing a blank on this one.

Anyone wanta to take a stab???

TIA

Phil

Re: Global Workbook/sheet referance

Yes, I could but I'm saying that I'd like to use this reference in multiple sub routines and would rather not have to do the Set each time. In fact in many cases the referenced workbook just shows up once in the sub. Therefore it wouldn't save much.

Any other idea's or am I not understanding your intent?

Thank you Norie for the quick response.

Phil

I have a situation where I reference a Profile sheet in a workbook that remains open.

I'd like to create a reference to that sheet that I can use throughout the code module. For example, instead of

SSOD.Range("Author").Value = Workbooks("FEC_Mstr.xls").Worksheets("Profile").Range("User").Value

I'd like to use something like

SSOD.Range("Author").Value = Profile.Range("User").Value

I'd rather do this with some kind of Function (or something else) and avoid the Global Variable thing, however I'm not sure how to code it.

Any help would be appreciated.

Thanks,

Phil

Re: A userform based Calender to COPY into your App!

Hi Gops,

Someone had mentioned that the code revisions that were done some time ago had resolved the European date issue. I guess from this post that they have not.

Maybe in the coming weeks I can look into that again and see if I can repair the problem.

Does anyone out there know the code to read the regional date settings on a machine? With this I could adapt what I have and make it more universal.

Another option would be to rewrite the code using the serial number date. Some work but doable.

Best regards,

Phil

Re: A userform based Calender to COPY into your App!

Hi Gops,

Someone had mentioned that the code revisions that were done some time ago had resolved the European date issue. I guess from this post that they have not.

Maybe in the coming weeks I can look into that again and see if I can repair the problem.

Does anyone out there know the code to read the regional date settings on a machine? With this I could adapt what I have and make it more universal.

Another option would be to rewrite the code using the serial number date. Some work but doable.

Best regards,

Phil

Re: A userform based Calender to COPY into your App!

Hi Gops,

Someone had mentioned that the code revisions that were done some time ago had resolved the European date issue. I guess from this post that they have not.

Maybe in the coming weeks I can look into that again and see if I can repair the problem.

Does anyone out there know the code to read the regional date settings on a machine? With this I could adapt what I have and make it more universal.

Another option would be to rewrite the code using the serial number date. Some work but doable.

Best regards,

Phil

Re: MapQuest from xl

Thanks TJ,

Good to hear from you old buddy.

Very best regards,

Phil

Re: Google search from xl

As promised the mapquest thread can be found here>>>
http://www.ozgrid.com/forum/sh…d.php?p=153579#post153579

Phil

In keeping to a previous thread I mentioned that I’d post a thread on creating a mapquest hyperlink via vba on the board so here goes.

The code I used is below and it works great but it’s set for the United States. I’ll explain in detail how to create for other countries below…

Sub Map_It()
    Dim CoRow, MySheet
    Set MySheet = Workbooks("Table.xls").Worksheets("Customer")
    CoRow = MySheet.Columns("A").Find(ActiveSheet.Range("Co_Name").Value, MatchCase:=True).Row
    ActiveWorkbook.FollowHyperlink Address:="http://www.mapquest.com/maps/map.adp?searchtype=address&country=US&addtohistory=&searchtab=" & _
        "home&address=" & Replace(MySheet.Cells(CoRow, 2), " ", "+") & _
        "&city=" & Replace(MySheet.Cells(CoRow, 3), " ", "+") & _
        "&state=" & Replace(MySheet.Cells(CoRow, 4), " ", "+") & _
        "&zipcode=" & Replace(Format(MySheet.Cells(CoRow, 5), "0####", " ", "+"), NewWindow:=True
    Set MySheet = Nothing
End Sub

To help gain understanding of what was done. I started by doing a search in mapquest for a specific address. Once the map was produced I copied the address line as it appeared in my browser, it looked like…

"http://www.mapquest.com/maps/map.adp?searchtype=address&country=US&addtohistory=&searchtab=home&address=One+Microsoft+Way&city=Redmond&state=WA&zipcode=98052"

Now, what I noticed was for each variable (address, city, state, etc.) the information that I had entered was listed. Yet all the spaces had been replaced with + signs.

So, I created the above code yet referencing/looking up in a database the value for each variable and pulled them into the hyperlink while at the same time replacing the spaces with + signs. Then invoked to appear in a separate window.

It does appear that this can be done for any country/address. However, one would have to search the site first to see what the address line looks like. i.e

"http://www.mapquest.com/maps/map.adp?formtype=address&searchtype=address&country=HU&addtohistory=&city=Budapest"

In the above example, I notice that the only variables are the City (Budapest). That may be true from all of Europe. If that is the case then the variables would be Country and City.

Anyway, I thought this was way cool. Next I’m going to work on gaining weather information and or travel information based on an address. If anyone is interested in that let me know and I’ll post the results.

Re: Google search from xl

Hi unandras,

I'd be happy to post something on the mapquest hyperlink and discribe it's details.

I'll start a new thread in this section so it can have it's own search specs within the site.

Quote

Is it applicable for Hungarian adresses also?

Well I just looked up a map on Budapest and ended up with a very detailed map of the city. In fact it appears that mapquest is very international.

Now, that means the HTTP address string will change but I think I'll be able to instruct through that.

Good luck.

Phil

This maybe old hat, but, I just discovered it and I think it's the coolest.

Private Sub CommandButton1_Click()
    Dim Srch
    Srch = InputBox("What would you like to search for?", "Internet Search Tool", ActiveCell.Value)
    ActiveWorkbook.FollowHyperlink Address:="http://www.google.com/search?hl=en&q=" & Srch, NewWindow:=True
End Sub

By placing a command button on your sheet this will access the internet/google in a seperate window and search for the chosen cell value or anything else placed in the input box.

I also did one for mapquest that maps an address placed in a sheet automatically. If anyone is interested in that one, let me know and I'll start a new thread with that one.

Phil

Re: combo box heading

Hi Peter,

I run into a simular problem which I handled 3 differant ways.

1. I included the header row in my array so the first items that showed up were the column headers.

2. I simply put labels above my list box to ID the columns. Those can be made visible or not with code depending what mode the list is in at the time.

and I think this is what your looking for

3. do not include the header row in your array yet set your listbox ColumnHeads property to true. This will create column heads using the row directly above the first row.

Hope this helps.

Phil

Re: Dates: US vs. Europe

Hey Greg,

Not sure if this is it, but, check to see how the original file/cells were formatted. Most users don't go to the trouble to "hard" format date cells. In stead they rely on the "Auto" format that may or may not be right.

Just a thought or starting place.

Phil