Multiple Helps Needed Please

[JoeSalmi]

Instead of creating a ton of threads and people starting to hate me for it I figured I could create this one and get try and get help from whoever on many parts of my spreadsheet.

First off let me say that the majority of my SS works fine but I am trying to improve on it and make this easier for my boss who spends a TON of time doing what should only take him minutes to do. BTW the boss is also my old man and I'm really trying to just make his life much easier.


FORM to CELLS

So next on my list is, updating the user form that I already have. Currently I am using 1 form and 1 text box in that form which sends text to cell A1. This works fine but now I need to use that same form (created a new text box (called txt_ship_loco)) that I need to send that text into cell B5.

Here is what I am currently using to add info into cell A1

Private Sub btnOK_NJ_Click()

    Dim sData As String
    Dim lRowNum As Long
    
    sData = txtShipName.Text
    ' Put the data in the current worksheet:
    If Cells(1, 1).Value = "" Then
        lRowNum = 1
    Else
        lRowNum = Sheet1.UsedRange.Rows.Count
    End If
    Cells(lRowNum, 1).Value = "TIMESHEET  -  " & sData
    ' Clear text box and set focus for next entry:
    txtShipName.Text = ""
    txtShipName.SetFocus
    Unload Me


End Sub

 

[Norie]

To do that shouldn't you be calling the 2nd sub from the button on the userform?

Every time you run the Run_All_Macros I think this is what's happening.

1st macro is executed
useform shown
.... userform stuff
userform closes
control passes back to Run_All_Macros
2nd macro is executed

From what you've said that doesn't seem to be doing things in the right order.

 

[JoeSalmi]

To do that shouldn't you be calling the 2nd sub from the button on the userform?

Hmmm Let me try that.

 

[JoeSalmi]

Yep, what I did was moved macro_2 at the end of the okay button.

Another question I have or I should say, can this be done?

In one of my cells I have a city and state combined ie. New York, NY or Boston, MS... Is there a way that I can link whatever is in that cell to a google search?

In a real world scenario the user would just click on that cell (for whatever city it shows) and it takes them to a map of that city,state? Basically like a local search?

 

[Norie]

Well it's pretty straightforward to create hyperlinks on a spreadsheet using values in cells.

There's the worksheet function HYPERLINK and you can add hyperlinks to cells using code.

The most important thing I suppose is to get the link right using the values you have to work with, in this case city and state.

 

[JoeSalmi]

Well I know how to create a simple hyperlink within a ss but the issue is I am not the only one that uses it.

What I need to do is create a macro that creates the hyperlink for me and also does a search for the city and state.

The Cell that I need it to hyperlink is Sheet1 (B5).

The user of the SS will enter the city and state using a form at the start of another macro and once they put in their location (which changes about every week) this macro will automatically turn that into a hyperlink that leads to a google map search.

Can you do that or can I try and get someone to do that for me please???

 

[JoeSalmi]

I was able to figure out how to do this...

It pulls the data that the user inputs from the user form called txt_ship_loco.Text

 Dim linky As Range
   
   ' you need to know which cell of which table
   Set linky = Sheet1.Range("B5")
   
   ' read the help topic on the Add method
   ' of the Hyperlinks collection
        Sheet1.Hyperlinks.Add Anchor:=linky, _
        Address:="http://maps.google.com/?q=" & txt_ship_loco.Text, _
        ScreenTip:="Local Search", _
        TextToDisplay:=txt_ship_loco.Text

 

[JoeSalmi]

NEW HELP NEEDED

Okay I need a little bit of help with this cause I just can't figure it out.

I am using this code to get my save as dialog to pop up which asks for a file name.

 With Application.Dialogs(xlDialogSaveAs).Show
       End With

But what I would like to have happen is the Save as box pop up with with this in the save as area:

ThisWorkbook.Name & "(" & Sheet1.Range("C4") & " " & Sheet1.Range("E4") & ")"

That way each week when the user saves the spreadsheet all they have to do is double check the name and click okay with out having to edit and type out another long name.

Can anyone help me out?

 

[Norie]

You really should start a new thread for each question - this doesn't seem to be linked to the one you wanted help with at the beginning.

Anyway, if you want a save as dialog to appear with a default filename it might be worth taking a look at Application.GetSaveAsFileName.

You can do it using Application.Dialog... but it's a little easier to do what you want with GetSaveAsFileName.

A simple example:

Dim varSaveAsName
 
    varSaveAsName = Application.GetSaveAsFilename(ThisWorkbook.Name & "(" & Sheet1.Range("C4") & " " & Sheet1.Range("E4") & ")")

 

[JoeSalmi]

You really should start a new thread for each question - this doesn't seem to be linked to the one you wanted help with at the beginning.

Anyway, if you want a save as dialog to appear with a default filename it might be worth taking a look at Application.GetSaveAsFileName.

You can do it using Application.Dialog... but it's a little easier to do what you want with GetSaveAsFileName.

A simple example:

Dim varSaveAsName
 
    varSaveAsName = Application.GetSaveAsFilename(ThisWorkbook.Name & "(" & Sheet1.Range("C4") & " " & Sheet1.Range("E4") & ")")

I tried something vary close to that and it didn't work. This isn't suggesting the new name like I want it to but the save as box does pop up

 

[Norie]

You really should start a new thread for this - and post the code that isn't working.