I am wanting to save a workbook using a macro that will create the filename
from concatenating the contents of 2 cells (C3 and C4 with a hyphen between
the text). This new workbook will be attached to a record in a software
program.

I will then need to save the workbook under its original name as I use a
second workbook that the first one uses for doing a number of lookups and it
looks to the origiinal name to transfer data.

I have created a macro to save the file under the original filename and
another one to delete the contents of most of the cells that are used to
input data.

If I could do this using Buttons on the workbook instead of CTRL ?, that
would probably be better for me and the 8 people who will be using these
workbooks.

Hi,

I'm not clear what your problem is exactly - you seem to have written
most of your code already - good for you. A few comments for you anyway:

Rather than saving under a new name and then again under the old name
you may like to take a look at SaveCopyAs - rather than SaveAs. (If you
then need to edit this file and delete some of the data you would need
to open it, save and close. But this is still neater - I would say -
than using SaveAs for the new name and then SaveAs for the original name
again.)

Re. use buttons, do you mean command bar button or a button on the
workbook(sheet) itself?

HTH,
Gareth



David Vollmer wrote:
I am wanting to save a workbook using a macro that will create the filename
from concatenating the contents of 2 cells (C3 and C4 with a hyphen between
the text). This new workbook will be attached to a record in a software
program.

I will then need to save the workbook under its original name as I use a
second workbook that the first one uses for doing a number of lookups and it
looks to the origiinal name to transfer data.

I have created a macro to save the file under the original filename and
another one to delete the contents of most of the cells that are used to
input data.

If I could do this using Buttons on the workbook instead of CTRL ?, that
would probably be better for me and the 8 people who will be using these
workbooks.

Gareth,

Thank you for your help and suggestions. Your suggestion to SaveCopyAs I
think will work much better. I was able to make three buttons that I placed
on the sheet but would be interested in having command bar buttons to do the
SaveCopyAs and clear data from the sheet.

Since I will have 8 people using these workbooks I would like it to be as
user-friendly as possible. For example since the main workbook depends on a
secondary workbook (that contains 53 sheets) to provide answers to the main
sheet via a series of Vlookups, I need to male sure that both sheets are
loaded before data is entered into the main sheet.

I have tried to figure out a way to spell check three of the cells (one
contains the name of a country and the other two have a city and state (or
region) in them. These three cells are the key to the multiple lookups and a
misspelling could cause a false response. For example, if a
city/st/country/region are not found, the lookups return an answer of 0
(which is the usual answer as most cities are not found). If found the
answers range between 1 and 9.

By the way, the purpose of these workbooks is to create a score for certain
businesses that are potentially high risk for money laundering or terrorists
funding activity. I am trying to automate the scoring process so that the 8
people entering data into the cells will get correct answers by merely
answering the questions (most of which are Yes or No). Currently they are
having to calculate each answer except for the total which is automated.

Your help is most appreciated!

"Gareth" wrote:

Hi,

I'm not clear what your problem is exactly - you seem to have written
most of your code already - good for you. A few comments for you anyway:

Rather than saving under a new name and then again under the old name
you may like to take a look at SaveCopyAs - rather than SaveAs. (If you
then need to edit this file and delete some of the data you would need
to open it, save and close. But this is still neater - I would say -
than using SaveAs for the new name and then SaveAs for the original name
again.)

Re. use buttons, do you mean command bar button or a button on the
workbook(sheet) itself?

HTH,
Gareth



David Vollmer wrote:
I am wanting to save a workbook using a macro that will create the filename
from concatenating the contents of 2 cells (C3 and C4 with a hyphen between
the text). This new workbook will be attached to a record in a software
program.

I will then need to save the workbook under its original name as I use a
second workbook that the first one uses for doing a number of lookups and it
looks to the origiinal name to transfer data.

I have created a macro to save the file under the original filename and
another one to delete the contents of most of the cells that are used to
input data.

If I could do this using Buttons on the workbook instead of CTRL ?, that
would probably be better for me and the 8 people who will be using these
workbooks.

Hi David,

I'm glad you found my comments useful. Here's some follow up info in
response to this post:

(1) Command Bar buttons.
Easy and simple to implement. My suggestion would be to create a new
command bar with the required buttons when you open your spreadsheet.
(Alternatively, you could add new buttons to an existing command bar.)

(a) Creating the command bar:
Place the following code in the ThisWorkbook module of your workbook:

Private Sub Workbook_Open()
fcnLoadCommandBar
End Sub

Now, either in the same module or in a different module place the
following code. (Or you could just place all of this code in the
Workbook_Open sub - I just like being tidy).

Private TOOLBAR_NAME as String = "myToolBar"

Function fcnCommandBarLoad() As Boolean

Dim NewCtrl As CommandBarControl

On Error GoTo Errorhandler_ToolbarExists
Application.CommandBars.Add(Name:=TOOLBAR_NAME, _
Position:=msoBarTop, _
Temporary:=True).Visible = True
On Error GoTo 0

With Application.CommandBars(TOOLBAR_NAME)

'create a control with an image
Set NewCtrl = .Controls.Add(Type:=msoControlButton)
With NewCtrl
.Caption = "&ClearCells"
.OnAction = "ClearSheetCells"
.Style = msoButtonIcon
.FaceId = 620
.TooltipText = "Clears my cells"
.BeginGroup = False
.Tag = "" 'you probably won't use this
End With

'create a control with a text caption
Set NewCtrl = .Controls.Add(Type:=msoControlButton)
With NewCtrl
.Caption = "SaveCop&y"
.OnAction = "SaveCopyOfWorkbook"
.Style = msoButtonCaption
.TooltipText = "Saves a copy of my workbook"
.BeginGroup = False
.Tag = "ALLUSERS,ACTIVEROSTERONLY"
End With
End With

Set NewCtrl = Nothing

'Skip ErrorHandling Section
Exit Function

Errorhandler_ToolbarExists:
'Let's delete it and rebuild....
Application.CommandBars(TOOLBAR_NAME).Delete
Resume

End Function


(b) Hide / Unhide Toolbar
Obviously, these buttons are context sensitive - you don't really need
them if your user is working on a different spreadsheet. Therefore maybe
you want to hide / unhide them depending on the workbook or even
worksheet which is active. To do this, place something like the below in
ThisWorbook.

Private Sub Workbook_Deactivate()
Application.CommandBars(TOOLBAR_NAME).Visible = False
End Sub

Obviously you need to capture the workbook activating to make it visible
again. Maybe you want to disable the ClearCells button when you're not
on the right sheet etc.


(2) Spellchecking:
Basically, I'm guessing, you want to have a predefined list right? And
check against this? You have a few options:
(a) Basic - Using Data Validation
- You could add STates, Countries etc. to a hidden column on the main
sheet and apply data validation to the appropriate cells. This would
allow the user to choose from a list of countries etc. You would need to
let the user overrule though. The list wouldn't be self maintaining though.

(b) As above but have a new button that would add to your list by
querying your secondary worksheet. (I'm not a big fan of data validation
- although it does have its uses.)

(c) Trap when a user updates a country/state/city cell. Look in the
second workbook (or rather an array in memory derived from that
workbook) to see if it's already present. If it is just accept it. If
not, flag a message to the user saying
"'Namibia' is a new country, are you sure it is correct?"
If they answer yes then accept it (and add it to the countries' array) -
else revert them back to the cell they were editing. You use a
Worksheet_Change event for this. (I'm afraid I don't know your level but
if you need a hand implementing something like this then let me know.)

HTH,
G


David Vollmer wrote:
Gareth,

Thank you for your help and suggestions. Your suggestion to SaveCopyAs I
think will work much better. I was able to make three buttons that I placed
on the sheet but would be interested in having command bar buttons to do the
SaveCopyAs and clear data from the sheet.

Since I will have 8 people using these workbooks I would like it to be as
user-friendly as possible. For example since the main workbook depends on a
secondary workbook (that contains 53 sheets) to provide answers to the main
sheet via a series of Vlookups, I need to male sure that both sheets are
loaded before data is entered into the main sheet.

I have tried to figure out a way to spell check three of the cells (one
contains the name of a country and the other two have a city and state (or
region) in them. These three cells are the key to the multiple lookups and a
misspelling could cause a false response. For example, if a
city/st/country/region are not found, the lookups return an answer of 0
(which is the usual answer as most cities are not found). If found the
answers range between 1 and 9.

By the way, the purpose of these workbooks is to create a score for certain
businesses that are potentially high risk for money laundering or terrorists
funding activity. I am trying to automate the scoring process so that the 8
people entering data into the cells will get correct answers by merely
answering the questions (most of which are Yes or No). Currently they are
having to calculate each answer except for the total which is automated.

Your help is most appreciated!

"Gareth" wrote:


Hi,

I'm not clear what your problem is exactly - you seem to have written
most of your code already - good for you. A few comments for you anyway:

Rather than saving under a new name and then again under the old name
you may like to take a look at SaveCopyAs - rather than SaveAs. (If you
then need to edit this file and delete some of the data you would need
to open it, save and close. But this is still neater - I would say -
than using SaveAs for the new name and then SaveAs for the original name
again.)

Re. use buttons, do you mean command bar button or a button on the
workbook(sheet) itself?

HTH,
Gareth



David Vollmer wrote:

I am wanting to save a workbook using a macro that will create the filename
from concatenating the contents of 2 cells (C3 and C4 with a hyphen between
the text). This new workbook will be attached to a record in a software
program.

I will then need to save the workbook under its original name as I use a
second workbook that the first one uses for doing a number of lookups and it
looks to the origiinal name to transfer data.

I have created a macro to save the file under the original filename and
another one to delete the contents of most of the cells that are used to
input data.

If I could do this using Buttons on the workbook instead of CTRL ?, that
would probably be better for me and the 8 people who will be using these
workbooks.

Gareth, I have the following in the Workbook Open event, but get an
error, "Object required"

Private Sub Workbook_Open()
fcnCommandBarLoad
End Sub
Private Sub Workbook_Deactivate()
Application.CommandBars(Toolbar_Name).Visible = False
End Sub
Private Sub Workbook_Activate()
Application.CommandBars(Toolbar_Name).Visible = True
End Sub
Function fcnCommandBarLoad() As Boolean

Dim NewCtrl As CommandBarControl
Dim Toolbar_Name As String

Set Toolbar_Name = "NotHitGL"

On Error GoTo Errorhandler_ToolbarExists
Application.CommandBars.Add(Name:=Toolbar_Name, _
Position:=msoBarTop, _
Temporary:=True).Visible = True
On Error GoTo 0

With Application.CommandBars(Toolbar_Name)

'create a control with an image
Set NewCtrl = .Controls.Add(Type:=msoControlButton)
With NewCtrl
.Caption = "&Not Posted"
.OnAction = "ProjectsNotPostedGL"
.Style = msoButtonIcon
.FaceId = 620
.TooltipText = "Shows projects not yet posted to GL"
.BeginGroup = False
.Tag = "" 'you probably won't use this
End With

Set NewCtrl = .Controls.Add(Type:=msoControlButton)
With NewCtrl
.Caption = "Convert to &Text"
.OnAction = "ConvertToText"
.Style = msoButtonIcon
.FaceId = 162
.TooltipText = "Converts formulas to text"
.BeginGroup = False
.Tag = "ALLUSERS"
End With
End With

Set NewCtrl = Nothing

'Skip ErrorHandling Section
Exit Function

Errorhandler_ToolbarExists:
'Let's delete it and rebuild....
Application.CommandBars(Toolbar_Name).Delete
Resume

End Function

Can you help. TIA

Greg

Hi,

You're using 'set', to assign the value "NotHitGL" to the string
variable Toolbar_Name. You only use 'set' with objects.

Just use

Toolbar_Name = "NotHitGL"

Or save a line by declaring it as a constant in the scope of your
procedure i.e. instead of:
Dim Toolbar_Name As String
use
Const Toolbar_Name As String = "NotHitGL"

I would recommend however (as I think I did in my post but I might be
mistaken) declaring it as a public constant by placing the below at the
top of a standard module.

Public Const TOOLBAR_NAME As String = "NotHitGL"

I prefer this approach since it means it you ever need to change the
toolbar name you only change it once - and it's good for all the
functions that use it.

Note, if you make it a constant, I write the constant in CAPS e.g.
Const MYTOOLBARNAME As String = "NotHitGL"
since, AFAIK, that's standard notation for constants.

HTH,
Gareth


GregR wrote:
Gareth, I have the following in the Workbook Open event, but get an
error, "Object required"

Private Sub Workbook_Open()
fcnCommandBarLoad
End Sub
Private Sub Workbook_Deactivate()
Application.CommandBars(Toolbar_Name).Visible = False
End Sub
Private Sub Workbook_Activate()
Application.CommandBars(Toolbar_Name).Visible = True
End Sub
Function fcnCommandBarLoad() As Boolean

Dim NewCtrl As CommandBarControl
Dim Toolbar_Name As String

Set Toolbar_Name = "NotHitGL"

On Error GoTo Errorhandler_ToolbarExists
Application.CommandBars.Add(Name:=Toolbar_Name, _
Position:=msoBarTop, _
Temporary:=True).Visible = True
On Error GoTo 0

With Application.CommandBars(Toolbar_Name)

'create a control with an image
Set NewCtrl = .Controls.Add(Type:=msoControlButton)
With NewCtrl
.Caption = "&Not Posted"
.OnAction = "ProjectsNotPostedGL"
.Style = msoButtonIcon
.FaceId = 620
.TooltipText = "Shows projects not yet posted to GL"
.BeginGroup = False
.Tag = "" 'you probably won't use this
End With

Set NewCtrl = .Controls.Add(Type:=msoControlButton)
With NewCtrl
.Caption = "Convert to &Text"
.OnAction = "ConvertToText"
.Style = msoButtonIcon
.FaceId = 162
.TooltipText = "Converts formulas to text"
.BeginGroup = False
.Tag = "ALLUSERS"
End With
End With

Set NewCtrl = Nothing

'Skip ErrorHandling Section
Exit Function

Errorhandler_ToolbarExists:
'Let's delete it and rebuild....
Application.CommandBars(Toolbar_Name).Delete
Resume

End Function

Can you help. TIA

Greg

Sorry - it was my original mistake wasn't it that you were correcting. I
declared
Private TOOLBAR_NAME as String = "myToolBar"
should have been
Private Const TOOLBAR_NAME as String = "myToolBar"
or
Public Const TOOLBAR_NAME as String = "myToolBar"
dpeending how you were using it.

Gareth,

You have given me a great deal of information regarding toolbars which I
will study and attempt to make happen. As you can probably tell, I am new to
programming and just now getting the hang of macros.

One thing I have noticed with macros is that if I stop or interrupt a macro
I get a message asking if I want to end, debug, etc. (or words to that
effect). Is there a way to trap an error in a macro like I can in a formula
so as to just automatically stop the macro without the message?

With regard to the spellchecking I don't need a list of countries, etc., I
just want to use the normal spellchecking that Excel provides. But I would
like to do it only with three cells, preferably when exiting each particular
cell. For example, if I type "Memphus" (instead of "Memphis") in the city
cell I would like it to spellcheck that cell when I leave it and do the same
thing for the state and country cells.

Your SaveCopyAs suggestion was great! I have now put 3 command buttons on
the sheet. One of the buttons loads the lookup tables workbook, one of them
is the SaveCopyAs button that utilizes the user entered data from two of the
cells and appends .xls to it, and the third button clears the data from the
sheet to prepare it for a new customer.

I have noticed a problem with links. Since 8 people will be using these two
workbooks I would like the lookup references to not change depending upon
where the workbooks are located on the computer. In other words, if the user
has stored both workbooks in a directory on their computer I would want the
lookup formulas to always know where they are so that the lookups will work
each time - even if they change the locations. Is there a way to do that? I
suppose the macros I created to load the lookup table and to SaveCopyAs might
need to be changed so that the macros will know where to look to open the
lookup workbook and where to SaveCopyAs.

I hope I have made sense with my rambling about what I would like to
accomplish.

Thank you again, Gareth, for your valuable help!

David

"Gareth" wrote:

Hi David,

I'm glad you found my comments useful. Here's some follow up info in
response to this post:

(1) Command Bar buttons.
Easy and simple to implement. My suggestion would be to create a new
command bar with the required buttons when you open your spreadsheet.
(Alternatively, you could add new buttons to an existing command bar.)

(a) Creating the command bar:
Place the following code in the ThisWorkbook module of your workbook:

Private Sub Workbook_Open()
fcnLoadCommandBar
End Sub

Now, either in the same module or in a different module place the
following code. (Or you could just place all of this code in the
Workbook_Open sub - I just like being tidy).

Private TOOLBAR_NAME as String = "myToolBar"

Function fcnCommandBarLoad() As Boolean

Dim NewCtrl As CommandBarControl

On Error GoTo Errorhandler_ToolbarExists
Application.CommandBars.Add(Name:=TOOLBAR_NAME, _
Position:=msoBarTop, _
Temporary:=True).Visible = True
On Error GoTo 0

With Application.CommandBars(TOOLBAR_NAME)

'create a control with an image
Set NewCtrl = .Controls.Add(Type:=msoControlButton)
With NewCtrl
.Caption = "&ClearCells"
.OnAction = "ClearSheetCells"
.Style = msoButtonIcon
.FaceId = 620
.TooltipText = "Clears my cells"
.BeginGroup = False
.Tag = "" 'you probably won't use this
End With

'create a control with a text caption
Set NewCtrl = .Controls.Add(Type:=msoControlButton)
With NewCtrl
.Caption = "SaveCop&y"
.OnAction = "SaveCopyOfWorkbook"
.Style = msoButtonCaption
.TooltipText = "Saves a copy of my workbook"
.BeginGroup = False
.Tag = "ALLUSERS,ACTIVEROSTERONLY"
End With
End With

Set NewCtrl = Nothing

'Skip ErrorHandling Section
Exit Function

Errorhandler_ToolbarExists:
'Let's delete it and rebuild....
Application.CommandBars(TOOLBAR_NAME).Delete
Resume

End Function


(b) Hide / Unhide Toolbar
Obviously, these buttons are context sensitive - you don't really need
them if your user is working on a different spreadsheet. Therefore maybe
you want to hide / unhide them depending on the workbook or even
worksheet which is active. To do this, place something like the below in
ThisWorbook.

Private Sub Workbook_Deactivate()
Application.CommandBars(TOOLBAR_NAME).Visible = False
End Sub

Obviously you need to capture the workbook activating to make it visible
again. Maybe you want to disable the ClearCells button when you're not
on the right sheet etc.


(2) Spellchecking:
Basically, I'm guessing, you want to have a predefined list right? And
check against this? You have a few options:
(a) Basic - Using Data Validation
- You could add STates, Countries etc. to a hidden column on the main
sheet and apply data validation to the appropriate cells. This would
allow the user to choose from a list of countries etc. You would need to
let the user overrule though. The list wouldn't be self maintaining though.

(b) As above but have a new button that would add to your list by
querying your secondary worksheet. (I'm not a big fan of data validation
- although it does have its uses.)

(c) Trap when a user updates a country/state/city cell. Look in the
second workbook (or rather an array in memory derived from that
workbook) to see if it's already present. If it is just accept it. If
not, flag a message to the user saying
"'Namibia' is a new country, are you sure it is correct?"
If they answer yes then accept it (and add it to the countries' array) -
else revert them back to the cell they were editing. You use a
Worksheet_Change event for this. (I'm afraid I don't know your level but
if you need a hand implementing something like this then let me know.)

HTH,
G


David Vollmer wrote:
Gareth,

Thank you for your help and suggestions. Your suggestion to SaveCopyAs I
think will work much better. I was able to make three buttons that I placed
on the sheet but would be interested in having command bar buttons to do the
SaveCopyAs and clear data from the sheet.

Since I will have 8 people using these workbooks I would like it to be as
user-friendly as possible. For example since the main workbook depends on a
secondary workbook (that contains 53 sheets) to provide answers to the main
sheet via a series of Vlookups, I need to male sure that both sheets are
loaded before data is entered into the main sheet.

I have tried to figure out a way to spell check three of the cells (one
contains the name of a country and the other two have a city and state (or
region) in them. These three cells are the key to the multiple lookups and a
misspelling could cause a false response. For example, if a
city/st/country/region are not found, the lookups return an answer of 0
(which is the usual answer as most cities are not found). If found the
answers range between 1 and 9.

By the way, the purpose of these workbooks is to create a score for certain
businesses that are potentially high risk for money laundering or terrorists
funding activity. I am trying to automate the scoring process so that the 8
people entering data into the cells will get correct answers by merely
answering the questions (most of which are Yes or No). Currently they are
having to calculate each answer except for the total which is automated.

Your help is most appreciated!

"Gareth" wrote:


Hi,

I'm not clear what your problem is exactly - you seem to have written
most of your code already - good for you. A few comments for you anyway:

Rather than saving under a new name and then again under the old name
you may like to take a look at SaveCopyAs - rather than SaveAs. (If you
then need to edit this file and delete some of the data you would need
to open it, save and close. But this is still neater - I would say -
than using SaveAs for the new name and then SaveAs for the original name
again.)

Re. use buttons, do you mean command bar button or a button on the
workbook(sheet) itself?

HTH,
Gareth



David Vollmer wrote:

I am wanting to save a workbook using a macro that will create the filename
from concatenating the contents of 2 cells (C3 and C4 with a hyphen between
the text). This new workbook will be attached to a record in a software
program.

I will then need to save the workbook under its original name as I use a
second workbook that the first one uses for doing a number of lookups and it
looks to the origiinal name to transfer data.

I have created a macro to save the file under the original filename and
another one to delete the contents of most of the cells that are used to
input data.

If I could do this using Buttons on the workbook instead of CTRL ?, that
would probably be better for me and the 8 people who will be using these
workbooks.

Gareth, I got it now and it works like a charm. Thank you very much.

Greg

Hi David,

I'm glad to be of assistance.

(1) Error Trapping
Do you mean trap so you can see what's going on? In which case just
click on DeBug - or set a break marker in your code by selecting the
line and pressing F9.

Or do you mean that you just want the macro to stop dead whenever it
encounters an error? Something like:

Sub Foo()

On Error Goto ErrorHandler:

'insert all your code here

ErrorHandler:

End Sub

But you might want to comment out the GoTo line until you've finished
writing your code otherwise you won't know what errors are occurring to
fix them!

Error handling is more sophisticated than just ignoring it though. See
"On Error Statement" in Help for more details. Note how in my COmmandBar
load function I use Error Trapping as an easy way to see if the
commandbar already exists.


(2) Spell Checking
You need to trap a change to your worksheet. Place the following code in
the code module for the worksheet you need to spellcheck on.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim rngToCheck As Range

'I don't know what cells you need to spellcheck, let's say these 3
Set rngToCheck = Union(Me.Range("E6:F6"), Me.Range("G5"))

'Check whether any of the changed cell(s) are ones we care about
Set rngToCheck = Intersect(Target, rngToCheck)
'if any of the changed range was in the range we needed to check
'go through them
If Not rngToCheck Is Nothing Then
rngToCheck.CheckSpelling IgnoreUppercase:=False, _
AlwaysSuggest:=True
Set rngToCheck = Nothing
End If

End Sub

NOTE: I tried this and it kept asking me whether I wanted to check the
rest of the sheet. Switching of Application.DisplayAlerts just meant it
checked them without me asking. I looked on the newsgroup and saw that
if you include the SpellLang argument then it resolves it e.g.
rngToCheck.CheckSpelling IgnoreUppercase:=False, _
AlwaysSuggest:=True, _
SpellLang:=1033
It does resolve it. I removed the SpellLang argument and it's no longer
asking so... you may like to add it.


(3) LINKS
I hate links! Um, I think you have two options he

(a) Always keep the two workbooks together. By this I mean that if you
have them both in c:\monkey\ and one references the other. If you move
them to c:\dog\ the references will still be valid.

(b) Place the WOrkbook that you need to reference on a network drive and
never move it. When you create the reference in your "form" workbook
reference it to the other. It will always remember.
NOTE: If you are using drive mappings, when referencing the other
workbook, make sure you have opened it via its UNC path NOT a mapped
drive i.e. go and find it in network neighborhood. Otherwise, if a user
has different drive mapping then it won't work.

Of course you could always use a macro to hunt down the workbook - but
that could take a while to scour all the available drives.

Have I understood your query correctly? I'm assuming that there's one
Reference Workbook (that everyone uses) and one Form style WOrkbook that
everyone has a copy of (although that could be on a network too and
jointly used). When you SaveCopyAs the copies will still link to the
Reference Workbook ok.

HTH,
Gareth

YOu're more than welcome.

GregR wrote:
Gareth, I got it now and it works like a charm. Thank you very much.

Greg

Yes, I am not very fond of LINKS either. I took your suggestion and moved the
lookup workbook to what is mapped on our computers as Z:\Scorecard Lookups. I
am seriously thinking about putting the other sheet (the FORM) there as well.
It seems to make the most sense.

I didn't understand "UNC path" and I am not sure how to get to and use
Network Neighborhood. Do I need to call our network guru or is it something I
can do myself?

I have noticed that one of my macros, the SaveAsCopy one, doesn't allow me
to choose WHERE I want to save the workbook. It appears to save it in the
last used directory. Is there some code I can put in the macro to prompt for
the directory when I run it?

The macro code I use for that is:

Sub SaveAsCustomerName()

On Error GoTo ErrorHandler:
'
' SaveAsCustomerName Macro
' Macro recorded 9/6/2005 by David Vollmer
'
ActiveWorkbook.SaveCopyAs Filename:=Range("C51").Value _
'
ErrorHandler:

End Sub

The reference to C51 is where I have the customer name-number.xls located.

I will work on the spellcheck method you provided probably this weekend. I
have typed the ErrorHandler code in one of my macros - Thanks. Still thinking
about the myToolBar routine. Right now the command buttons on the form seem
to be doing the job. But I will experiment with your toolbar code and see if
I can make my form more professional. This is a huge bank I am doing this for
and at present we are all temporary employees hoping to be hired sometime
next year!

Again, your help is most appreciated. I see that you are very active in this
discussion group and have helped many people. That's great!


"Gareth" wrote:

Hi David,

I'm glad to be of assistance.

(1) Error Trapping
Do you mean trap so you can see what's going on? In which case just
click on DeBug - or set a break marker in your code by selecting the
line and pressing F9.

Or do you mean that you just want the macro to stop dead whenever it
encounters an error? Something like:

Sub Foo()

On Error Goto ErrorHandler:

'insert all your code here

ErrorHandler:

End Sub

But you might want to comment out the GoTo line until you've finished
writing your code otherwise you won't know what errors are occurring to
fix them!

Error handling is more sophisticated than just ignoring it though. See
"On Error Statement" in Help for more details. Note how in my COmmandBar
load function I use Error Trapping as an easy way to see if the
commandbar already exists.


(2) Spell Checking
You need to trap a change to your worksheet. Place the following code in
the code module for the worksheet you need to spellcheck on.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim rngToCheck As Range

'I don't know what cells you need to spellcheck, let's say these 3
Set rngToCheck = Union(Me.Range("E6:F6"), Me.Range("G5"))

'Check whether any of the changed cell(s) are ones we care about
Set rngToCheck = Intersect(Target, rngToCheck)
'if any of the changed range was in the range we needed to check
'go through them
If Not rngToCheck Is Nothing Then
rngToCheck.CheckSpelling IgnoreUppercase:=False, _
AlwaysSuggest:=True
Set rngToCheck = Nothing
End If

End Sub

NOTE: I tried this and it kept asking me whether I wanted to check the
rest of the sheet. Switching of Application.DisplayAlerts just meant it
checked them without me asking. I looked on the newsgroup and saw that
if you include the SpellLang argument then it resolves it e.g.
rngToCheck.CheckSpelling IgnoreUppercase:=False, _
AlwaysSuggest:=True, _
SpellLang:=1033
It does resolve it. I removed the SpellLang argument and it's no longer
asking so... you may like to add it.


(3) LINKS
I hate links! Um, I think you have two options he

(a) Always keep the two workbooks together. By this I mean that if you
have them both in c:\monkey\ and one references the other. If you move
them to c:\dog\ the references will still be valid.

(b) Place the WOrkbook that you need to reference on a network drive and
never move it. When you create the reference in your "form" workbook
reference it to the other. It will always remember.
NOTE: If you are using drive mappings, when referencing the other
workbook, make sure you have opened it via its UNC path NOT a mapped
drive i.e. go and find it in network neighborhood. Otherwise, if a user
has different drive mapping then it won't work.

Of course you could always use a macro to hunt down the workbook - but
that could take a while to scour all the available drives.

Have I understood your query correctly? I'm assuming that there's one
Reference Workbook (that everyone uses) and one Form style WOrkbook that
everyone has a copy of (although that could be on a network too and
jointly used). When you SaveCopyAs the copies will still link to the
Reference Workbook ok.

HTH,
Gareth

Hi David,

- UNC PATHS -
By using a UNC path you're specifying directly where on the network a
file you should be saved - rather than relying on the drive mapping. I
prefer this (it's a personal thing) because it means if the drive
mapping changes - there'll be no problems.

For example, your "Z:\Scorecard Lookups" directory could be accessed by
something like:

"\\servername\shareddata\Scorecard Lookups" - thus this path is drive
mapping agnostic.

If you're ever using VBA to access a network file I would highly
recommend using this method of addressing it. Note that links (and
indeed VBA project references) have a nasty habit of reverting to drive
mapped locations. By this I mean, if you open a workbook by accessing it
via the mapped drive it often surreptitiously changes the link to
"z:\etc etc." even if it was "\\server\etc." originally. That can be a
headache.

All this said, if you're reasonably confident that mapping won't change
(too often) and workbooks won't be copied locally and remain used on the
network you shouldn't be too preoccupied by all this.

There is pre-written VBA code (search this NG for getUNC or something)
that returns the UNC path from a mapped drive. But I don't think you
need it here.


- SAVECOPYAS -
You specify the location by stating the full path in the Filename
argument of SaveCopyAs. e.g.
ActiveWorkbook.SaveCopyAs Filename:="c:\temp\" & Range("C51").Value

If you want to save in the same location as the original, try something
like:

With ActiveWorkbook
.SaveCopyAs Filename:= .Path & "\" & Range("C51").Value
End with
(I'm using the With/End With just to keep things tidy - it's not necessary.)

Should you want to allow the user to choose a folder to save it in, a
simpler way to achieve this is using Application.GetSaveAsFilename

In your case, do something like

Sub SaveMe()
Dim mySaveName As Variant

mySaveName = Application.GetSaveAsFilename( _
InitialFileName:=Range("C51").Value, _
FileFilter:="Excel Files (*.xls), *.xls")

If mySaveName < False Then
'save your file!
End If

End Sub

Note, you could also specify the original save directory above e.g.
.... InitialFileName:="c:\temp\" & Range("C51").Value
Should make less browsing for the user. Obviously, if they choose a
different folder you could always remember it and, next time they save,
start off in that folder.

It is of course also possible to allow them to choose the save folder
without allowing them to change the filename. Either by simply stripping
out the filename from mySaveName and just using the path they chose or
by giving them a dedicated folder select dialogue box - but that is more
complicated because you need APIs.

- BUTTONS -
I like command bars (still hate links!). IMHO I wouldn't, however, say
they're really that useful here. You don't really need want to occupy
too much of the user's screen real estate and to be tidy, you should
hide or delete them when you close or deactivate your workbook - that's
a bit of fathing around for no real gain in this instance. (I prefer to
use them when I use an AddIn.)


Thanks for your kind words - I'm not really that active here. There are
plenty of real regulars. I just tend to whizz through and see who hasn't
been answered for a while and help them - if I can!

I hope this helps again. And good luck in getting a permanent job!

Gareth

David Vollmer wrote:
Yes, I am not very fond of LINKS either. I took your suggestion and moved the
lookup workbook to what is mapped on our computers as Z:\Scorecard Lookups. I
am seriously thinking about putting the other sheet (the FORM) there as well.
It seems to make the most sense.

I didn't understand "UNC path" and I am not sure how to get to and use
Network Neighborhood. Do I need to call our network guru or is it something I
can do myself?

I have noticed that one of my macros, the SaveAsCopy one, doesn't allow me
to choose WHERE I want to save the workbook. It appears to save it in the
last used directory. Is there some code I can put in the macro to prompt for
the directory when I run it?

The macro code I use for that is:

Sub SaveAsCustomerName()

On Error GoTo ErrorHandler:
'
' SaveAsCustomerName Macro
' Macro recorded 9/6/2005 by David Vollmer
'
ActiveWorkbook.SaveCopyAs Filename:=Range("C51").Value _
'
ErrorHandler:

End Sub

The reference to C51 is where I have the customer name-number.xls located.

I will work on the spellcheck method you provided probably this weekend. I
have typed the ErrorHandler code in one of my macros - Thanks. Still thinking
about the myToolBar routine. Right now the command buttons on the form seem
to be doing the job. But I will experiment with your toolbar code and see if
I can make my form more professional. This is a huge bank I am doing this for
and at present we are all temporary employees hoping to be hired sometime
next year!

Again, your help is most appreciated. I see that you are very active in this
discussion group and have helped many people. That's great!


"Gareth" wrote:


Hi David,

I'm glad to be of assistance.

(1) Error Trapping
Do you mean trap so you can see what's going on? In which case just
click on DeBug - or set a break marker in your code by selecting the
line and pressing F9.

Or do you mean that you just want the macro to stop dead whenever it
encounters an error? Something like:

Sub Foo()

On Error Goto ErrorHandler:

'insert all your code here

ErrorHandler:

End Sub

But you might want to comment out the GoTo line until you've finished
writing your code otherwise you won't know what errors are occurring to
fix them!

Error handling is more sophisticated than just ignoring it though. See
"On Error Statement" in Help for more details. Note how in my COmmandBar
load function I use Error Trapping as an easy way to see if the
commandbar already exists.


(2) Spell Checking
You need to trap a change to your worksheet. Place the following code in
the code module for the worksheet you need to spellcheck on.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim rngToCheck As Range

'I don't know what cells you need to spellcheck, let's say these 3
Set rngToCheck = Union(Me.Range("E6:F6"), Me.Range("G5"))

'Check whether any of the changed cell(s) are ones we care about
Set rngToCheck = Intersect(Target, rngToCheck)
'if any of the changed range was in the range we needed to check
'go through them
If Not rngToCheck Is Nothing Then
rngToCheck.CheckSpelling IgnoreUppercase:=False, _
AlwaysSuggest:=True
Set rngToCheck = Nothing
End If

End Sub

NOTE: I tried this and it kept asking me whether I wanted to check the
rest of the sheet. Switching of Application.DisplayAlerts just meant it
checked them without me asking. I looked on the newsgroup and saw that
if you include the SpellLang argument then it resolves it e.g.
rngToCheck.CheckSpelling IgnoreUppercase:=False, _
AlwaysSuggest:=True, _
SpellLang:=1033
It does resolve it. I removed the SpellLang argument and it's no longer
asking so... you may like to add it.


(3) LINKS
I hate links! Um, I think you have two options he

(a) Always keep the two workbooks together. By this I mean that if you
have them both in c:\monkey\ and one references the other. If you move
them to c:\dog\ the references will still be valid.

(b) Place the WOrkbook that you need to reference on a network drive and
never move it. When you create the reference in your "form" workbook
reference it to the other. It will always remember.
NOTE: If you are using drive mappings, when referencing the other
workbook, make sure you have opened it via its UNC path NOT a mapped
drive i.e. go and find it in network neighborhood. Otherwise, if a user
has different drive mapping then it won't work.

Of course you could always use a macro to hunt down the workbook - but
that could take a while to scour all the available drives.

Have I understood your query correctly? I'm assuming that there's one
Reference Workbook (that everyone uses) and one Form style WOrkbook that
everyone has a copy of (although that could be on a network too and
jointly used). When you SaveCopyAs the copies will still link to the
Reference Workbook ok.

HTH,
Gareth

Gareth,

I can't get the SaveMe code to work.
What I have entered is:

Sub SaveMeAsCustomerName()
Dim mySaveName As Variant

mySaveName = Application.GetSaveAsFilename( _
InitialFileName:=Range("C51").Value, _
FileFilter:="Excel Files (*.xls), *.xls")

If mySaveName < False Then
'save your file!
End If

End Sub

It appears to work but then I can not find the "saved file" anywhere. What
have I done wrong? It doesn't seem to make any difference what directory I
choose or whether I change the C51 name.

Thank you!


"Gareth" wrote:

Hi David,

- UNC PATHS -
By using a UNC path you're specifying directly where on the network a
file you should be saved - rather than relying on the drive mapping. I
prefer this (it's a personal thing) because it means if the drive
mapping changes - there'll be no problems.

For example, your "Z:\Scorecard Lookups" directory could be accessed by
something like:

"\\servername\shareddata\Scorecard Lookups" - thus this path is drive
mapping agnostic.

If you're ever using VBA to access a network file I would highly
recommend using this method of addressing it. Note that links (and
indeed VBA project references) have a nasty habit of reverting to drive
mapped locations. By this I mean, if you open a workbook by accessing it
via the mapped drive it often surreptitiously changes the link to
"z:\etc etc." even if it was "\\server\etc." originally. That can be a
headache.

All this said, if you're reasonably confident that mapping won't change
(too often) and workbooks won't be copied locally and remain used on the
network you shouldn't be too preoccupied by all this.

There is pre-written VBA code (search this NG for getUNC or something)
that returns the UNC path from a mapped drive. But I don't think you
need it here.


- SAVECOPYAS -
You specify the location by stating the full path in the Filename
argument of SaveCopyAs. e.g.
ActiveWorkbook.SaveCopyAs Filename:="c:\temp\" & Range("C51").Value

If you want to save in the same location as the original, try something
like:

With ActiveWorkbook
.SaveCopyAs Filename:= .Path & "\" & Range("C51").Value
End with
(I'm using the With/End With just to keep things tidy - it's not necessary.)

Should you want to allow the user to choose a folder to save it in, a
simpler way to achieve this is using Application.GetSaveAsFilename

In your case, do something like

Sub SaveMe()
Dim mySaveName As Variant

mySaveName = Application.GetSaveAsFilename( _
InitialFileName:=Range("C51").Value, _
FileFilter:="Excel Files (*.xls), *.xls")

If mySaveName < False Then
'save your file!
End If

End Sub

Note, you could also specify the original save directory above e.g.
.... InitialFileName:="c:\temp\" & Range("C51").Value
Should make less browsing for the user. Obviously, if they choose a
different folder you could always remember it and, next time they save,
start off in that folder.

It is of course also possible to allow them to choose the save folder
without allowing them to change the filename. Either by simply stripping
out the filename from mySaveName and just using the path they chose or
by giving them a dedicated folder select dialogue box - but that is more
complicated because you need APIs.

- BUTTONS -
I like command bars (still hate links!). IMHO I wouldn't, however, say
they're really that useful here. You don't really need want to occupy
too much of the user's screen real estate and to be tidy, you should
hide or delete them when you close or deactivate your workbook - that's
a bit of fathing around for no real gain in this instance. (I prefer to
use them when I use an AddIn.)


Thanks for your kind words - I'm not really that active here. There are
plenty of real regulars. I just tend to whizz through and see who hasn't
been answered for a while and help them - if I can!

I hope this helps again. And good luck in getting a permanent job!

Gareth

David Vollmer wrote:
Yes, I am not very fond of LINKS either. I took your suggestion and moved the
lookup workbook to what is mapped on our computers as Z:\Scorecard Lookups. I
am seriously thinking about putting the other sheet (the FORM) there as well.
It seems to make the most sense.

I didn't understand "UNC path" and I am not sure how to get to and use
Network Neighborhood. Do I need to call our network guru or is it something I
can do myself?

I have noticed that one of my macros, the SaveAsCopy one, doesn't allow me
to choose WHERE I want to save the workbook. It appears to save it in the
last used directory. Is there some code I can put in the macro to prompt for
the directory when I run it?

The macro code I use for that is:

Sub SaveAsCustomerName()

On Error GoTo ErrorHandler:
'
' SaveAsCustomerName Macro
' Macro recorded 9/6/2005 by David Vollmer
'
ActiveWorkbook.SaveCopyAs Filename:=Range("C51").Value _
'
ErrorHandler:

End Sub

The reference to C51 is where I have the customer name-number.xls located.

I will work on the spellcheck method you provided probably this weekend. I
have typed the ErrorHandler code in one of my macros - Thanks. Still thinking
about the myToolBar routine. Right now the command buttons on the form seem
to be doing the job. But I will experiment with your toolbar code and see if
I can make my form more professional. This is a huge bank I am doing this for
and at present we are all temporary employees hoping to be hired sometime
next year!

Again, your help is most appreciated. I see that you are very active in this
discussion group and have helped many people. That's great!


"Gareth" wrote:


Hi David,

I'm glad to be of assistance.

(1) Error Trapping
Do you mean trap so you can see what's going on? In which case just
click on DeBug - or set a break marker in your code by selecting the
line and pressing F9.

Or do you mean that you just want the macro to stop dead whenever it
encounters an error? Something like:

Sub Foo()

On Error Goto ErrorHandler:

'insert all your code here

ErrorHandler:

End Sub

But you might want to comment out the GoTo line until you've finished
writing your code otherwise you won't know what errors are occurring to
fix them!

Error handling is more sophisticated than just ignoring it though. See
"On Error Statement" in Help for more details. Note how in my COmmandBar
load function I use Error Trapping as an easy way to see if the
commandbar already exists.


(2) Spell Checking
You need to trap a change to your worksheet. Place the following code in
the code module for the worksheet you need to spellcheck on.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim rngToCheck As Range

'I don't know what cells you need to spellcheck, let's say these 3
Set rngToCheck = Union(Me.Range("E6:F6"), Me.Range("G5"))

'Check whether any of the changed cell(s) are ones we care about
Set rngToCheck = Intersect(Target, rngToCheck)
'if any of the changed range was in the range we needed to check
'go through them
If Not rngToCheck Is Nothing Then
rngToCheck.CheckSpelling IgnoreUppercase:=False, _
AlwaysSuggest:=True
Set rngToCheck = Nothing
End If

End Sub

NOTE: I tried this and it kept asking me whether I wanted to check the
rest of the sheet. Switching of Application.DisplayAlerts just meant it
checked them without me asking. I looked on the newsgroup and saw that
if you include the SpellLang argument then it resolves it e.g.
rngToCheck.CheckSpelling IgnoreUppercase:=False, _
AlwaysSuggest:=True, _
SpellLang:=1033
It does resolve it. I removed the SpellLang argument and it's no longer
asking so... you may like to add it.


(3) LINKS
I hate links! Um, I think you have two options he

(a) Always keep the two workbooks together. By this I mean that if you
have them both in c:\monkey\ and one references the other. If you move
them to c:\dog\ the references will still be valid.

(b) Place the WOrkbook that you need to reference on a network drive and
never move it. When you create the reference in your "form" workbook
reference it to the other. It will always remember.
NOTE: If you are using drive mappings, when referencing the other
workbook, make sure you have opened it via its UNC path NOT a mapped
drive i.e. go and find it in network neighborhood. Otherwise, if a user
has different drive mapping then it won't work.

Of course you could always use a macro to hunt down the workbook - but
that could take a while to scour all the available drives.

Have I understood your query correctly? I'm assuming that there's one
Reference Workbook (that everyone uses) and one Form style WOrkbook that
everyone has a copy of (although that could be on a network too and
jointly used). When you SaveCopyAs the copies will still link to the
Reference Workbook ok.

HTH,
Gareth

:-)

I didn't write the line to save the file - thought I'd leave that for
you - depending on how you wanted to save the file!

I've written it in below

Sub SaveMeAsCustomerName()
Dim mySaveName As Variant

mySaveName = Application.GetSaveAsFilename( _
InitialFileName:=Range("C51").Value, _
FileFilter:="Excel Files (*.xls), *.xls")

If mySaveName < False Then
'save your file!
'NEW LINE BELOW (change ThisWorkbook to ActiveWorkbook or
'other workbook as you see fit.
Thisworkbook.SaveCopyAs FileName:=mySaveName
End If

End Sub

cya,
Gareth


David Vollmer wrote:
Gareth,

I can't get the SaveMe code to work.
What I have entered is:

Sub SaveMeAsCustomerName()
Dim mySaveName As Variant

mySaveName = Application.GetSaveAsFilename( _
InitialFileName:=Range("C51").Value, _
FileFilter:="Excel Files (*.xls), *.xls")

If mySaveName < False Then
'save your file!
End If

End Sub

It appears to work but then I can not find the "saved file" anywhere. What
have I done wrong? It doesn't seem to make any difference what directory I
choose or whether I change the C51 name.

Thank you!


"Gareth" wrote:


Hi David,

- UNC PATHS -
By using a UNC path you're specifying directly where on the network a
file you should be saved - rather than relying on the drive mapping. I
prefer this (it's a personal thing) because it means if the drive
mapping changes - there'll be no problems.

For example, your "Z:\Scorecard Lookups" directory could be accessed by
something like:

"\\servername\shareddata\Scorecard Lookups" - thus this path is drive
mapping agnostic.

If you're ever using VBA to access a network file I would highly
recommend using this method of addressing it. Note that links (and
indeed VBA project references) have a nasty habit of reverting to drive
mapped locations. By this I mean, if you open a workbook by accessing it
via the mapped drive it often surreptitiously changes the link to
"z:\etc etc." even if it was "\\server\etc." originally. That can be a
headache.

All this said, if you're reasonably confident that mapping won't change
(too often) and workbooks won't be copied locally and remain used on the
network you shouldn't be too preoccupied by all this.

There is pre-written VBA code (search this NG for getUNC or something)
that returns the UNC path from a mapped drive. But I don't think you
need it here.


- SAVECOPYAS -
You specify the location by stating the full path in the Filename
argument of SaveCopyAs. e.g.
ActiveWorkbook.SaveCopyAs Filename:="c:\temp\" & Range("C51").Value

If you want to save in the same location as the original, try something
like:

With ActiveWorkbook
.SaveCopyAs Filename:= .Path & "\" & Range("C51").Value
End with
(I'm using the With/End With just to keep things tidy - it's not necessary.)

Should you want to allow the user to choose a folder to save it in, a
simpler way to achieve this is using Application.GetSaveAsFilename

In your case, do something like

Sub SaveMe()
Dim mySaveName As Variant

mySaveName = Application.GetSaveAsFilename( _
InitialFileName:=Range("C51").Value, _
FileFilter:="Excel Files (*.xls), *.xls")

If mySaveName < False Then
'save your file!
End If

End Sub

Note, you could also specify the original save directory above e.g.
.... InitialFileName:="c:\temp\" & Range("C51").Value
Should make less browsing for the user. Obviously, if they choose a
different folder you could always remember it and, next time they save,
start off in that folder.

It is of course also possible to allow them to choose the save folder
without allowing them to change the filename. Either by simply stripping
out the filename from mySaveName and just using the path they chose or
by giving them a dedicated folder select dialogue box - but that is more
complicated because you need APIs.

- BUTTONS -
I like command bars (still hate links!). IMHO I wouldn't, however, say
they're really that useful here. You don't really need want to occupy
too much of the user's screen real estate and to be tidy, you should
hide or delete them when you close or deactivate your workbook - that's
a bit of fathing around for no real gain in this instance. (I prefer to
use them when I use an AddIn.)


Thanks for your kind words - I'm not really that active here. There are
plenty of real regulars. I just tend to whizz through and see who hasn't
been answered for a while and help them - if I can!

I hope this helps again. And good luck in getting a permanent job!

Gareth

David Vollmer wrote:

Yes, I am not very fond of LINKS either. I took your suggestion and moved the
lookup workbook to what is mapped on our computers as Z:\Scorecard Lookups. I
am seriously thinking about putting the other sheet (the FORM) there as well.
It seems to make the most sense.

I didn't understand "UNC path" and I am not sure how to get to and use
Network Neighborhood. Do I need to call our network guru or is it something I
can do myself?

I have noticed that one of my macros, the SaveAsCopy one, doesn't allow me
to choose WHERE I want to save the workbook. It appears to save it in the
last used directory. Is there some code I can put in the macro to prompt for
the directory when I run it?

The macro code I use for that is:

Sub SaveAsCustomerName()

On Error GoTo ErrorHandler:
'
' SaveAsCustomerName Macro
' Macro recorded 9/6/2005 by David Vollmer
'
ActiveWorkbook.SaveCopyAs Filename:=Range("C51").Value _
'
ErrorHandler:

End Sub

The reference to C51 is where I have the customer name-number.xls located.

I will work on the spellcheck method you provided probably this weekend. I
have typed the ErrorHandler code in one of my macros - Thanks. Still thinking
about the myToolBar routine. Right now the command buttons on the form seem
to be doing the job. But I will experiment with your toolbar code and see if
I can make my form more professional. This is a huge bank I am doing this for
and at present we are all temporary employees hoping to be hired sometime
next year!

Again, your help is most appreciated. I see that you are very active in this
discussion group and have helped many people. That's great!


"Gareth" wrote:



Hi David,

I'm glad to be of assistance.

(1) Error Trapping
Do you mean trap so you can see what's going on? In which case just
click on DeBug - or set a break marker in your code by selecting the
line and pressing F9.

Or do you mean that you just want the macro to stop dead whenever it
encounters an error? Something like:

Sub Foo()

On Error Goto ErrorHandler:

'insert all your code here

ErrorHandler:

End Sub

But you might want to comment out the GoTo line until you've finished
writing your code otherwise you won't know what errors are occurring to
fix them!

Error handling is more sophisticated than just ignoring it though. See
"On Error Statement" in Help for more details. Note how in my COmmandBar
load function I use Error Trapping as an easy way to see if the
commandbar already exists.


(2) Spell Checking
You need to trap a change to your worksheet. Place the following code in
the code module for the worksheet you need to spellcheck on.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim rngToCheck As Range

'I don't know what cells you need to spellcheck, let's say these 3
Set rngToCheck = Union(Me.Range("E6:F6"), Me.Range("G5"))

'Check whether any of the changed cell(s) are ones we care about
Set rngToCheck = Intersect(Target, rngToCheck)
'if any of the changed range was in the range we needed to check
'go through them
If Not rngToCheck Is Nothing Then
rngToCheck.CheckSpelling IgnoreUppercase:=False, _
AlwaysSuggest:=True
Set rngToCheck = Nothing
End If

End Sub

NOTE: I tried this and it kept asking me whether I wanted to check the
rest of the sheet. Switching of Application.DisplayAlerts just meant it
checked them without me asking. I looked on the newsgroup and saw that
if you include the SpellLang argument then it resolves it e.g.
rngToCheck.CheckSpelling IgnoreUppercase:=False, _
AlwaysSuggest:=True, _
SpellLang:=1033
It does resolve it. I removed the SpellLang argument and it's no longer
asking so... you may like to add it.


(3) LINKS
I hate links! Um, I think you have two options he

(a) Always keep the two workbooks together. By this I mean that if you
have them both in c:\monkey\ and one references the other. If you move
them to c:\dog\ the references will still be valid.

(b) Place the WOrkbook that you need to reference on a network drive and
never move it. When you create the reference in your "form" workbook
reference it to the other. It will always remember.
NOTE: If you are using drive mappings, when referencing the other
workbook, make sure you have opened it via its UNC path NOT a mapped
drive i.e. go and find it in network neighborhood. Otherwise, if a user
has different drive mapping then it won't work.

Of course you could always use a macro to hunt down the workbook - but
that could take a while to scour all the available drives.

Have I understood your query correctly? I'm assuming that there's one
Reference Workbook (that everyone uses) and one Form style WOrkbook that
everyone has a copy of (although that could be on a network too and
jointly used). When you SaveCopyAs the copies will still link to the
Reference Workbook ok.

HTH,
Gareth

Sorry, Gareth, I noticed the missing code and felt really stupid! I corrected
it using ActiveWorkbook and it works fine. If, however, there is an existing
workbook with the same name in the chosen directory it will overwrite it
without giving me a warning. I have searched the discussion group for a way
to have the option of overwriting or changing the name to prevent that from
happening but have not been successful yet.

Thanks again!
David

"Gareth" wrote:

:-)

I didn't write the line to save the file - thought I'd leave that for
you - depending on how you wanted to save the file!

I've written it in below

Sub SaveMeAsCustomerName()
Dim mySaveName As Variant

mySaveName = Application.GetSaveAsFilename( _
InitialFileName:=Range("C51").Value, _
FileFilter:="Excel Files (*.xls), *.xls")

If mySaveName < False Then
'save your file!
'NEW LINE BELOW (change ThisWorkbook to ActiveWorkbook or
'other workbook as you see fit.
Thisworkbook.SaveCopyAs FileName:=mySaveName
End If

End Sub

cya,
Gareth


David Vollmer wrote:
Gareth,

I can't get the SaveMe code to work.
What I have entered is:

Sub SaveMeAsCustomerName()
Dim mySaveName As Variant

mySaveName = Application.GetSaveAsFilename( _
InitialFileName:=Range("C51").Value, _
FileFilter:="Excel Files (*.xls), *.xls")

If mySaveName < False Then
'save your file!
End If

End Sub

It appears to work but then I can not find the "saved file" anywhere. What
have I done wrong? It doesn't seem to make any difference what directory I
choose or whether I change the C51 name.

Thank you!


"Gareth" wrote:


Hi David,

- UNC PATHS -
By using a UNC path you're specifying directly where on the network a
file you should be saved - rather than relying on the drive mapping. I
prefer this (it's a personal thing) because it means if the drive
mapping changes - there'll be no problems.

For example, your "Z:\Scorecard Lookups" directory could be accessed by
something like:

"\\servername\shareddata\Scorecard Lookups" - thus this path is drive
mapping agnostic.

If you're ever using VBA to access a network file I would highly
recommend using this method of addressing it. Note that links (and
indeed VBA project references) have a nasty habit of reverting to drive
mapped locations. By this I mean, if you open a workbook by accessing it
via the mapped drive it often surreptitiously changes the link to
"z:\etc etc." even if it was "\\server\etc." originally. That can be a
headache.

All this said, if you're reasonably confident that mapping won't change
(too often) and workbooks won't be copied locally and remain used on the
network you shouldn't be too preoccupied by all this.

There is pre-written VBA code (search this NG for getUNC or something)
that returns the UNC path from a mapped drive. But I don't think you
need it here.


- SAVECOPYAS -
You specify the location by stating the full path in the Filename
argument of SaveCopyAs. e.g.
ActiveWorkbook.SaveCopyAs Filename:="c:\temp\" & Range("C51").Value

If you want to save in the same location as the original, try something
like:

With ActiveWorkbook
.SaveCopyAs Filename:= .Path & "\" & Range("C51").Value
End with
(I'm using the With/End With just to keep things tidy - it's not necessary.)

Should you want to allow the user to choose a folder to save it in, a
simpler way to achieve this is using Application.GetSaveAsFilename

In your case, do something like

Sub SaveMe()
Dim mySaveName As Variant

mySaveName = Application.GetSaveAsFilename( _
InitialFileName:=Range("C51").Value, _
FileFilter:="Excel Files (*.xls), *.xls")

If mySaveName < False Then
'save your file!
End If

End Sub

Note, you could also specify the original save directory above e.g.
.... InitialFileName:="c:\temp\" & Range("C51").Value
Should make less browsing for the user. Obviously, if they choose a
different folder you could always remember it and, next time they save,
start off in that folder.

It is of course also possible to allow them to choose the save folder
without allowing them to change the filename. Either by simply stripping
out the filename from mySaveName and just using the path they chose or
by giving them a dedicated folder select dialogue box - but that is more
complicated because you need APIs.

- BUTTONS -
I like command bars (still hate links!). IMHO I wouldn't, however, say
they're really that useful here. You don't really need want to occupy
too much of the user's screen real estate and to be tidy, you should
hide or delete them when you close or deactivate your workbook - that's
a bit of fathing around for no real gain in this instance. (I prefer to
use them when I use an AddIn.)


Thanks for your kind words - I'm not really that active here. There are
plenty of real regulars. I just tend to whizz through and see who hasn't
been answered for a while and help them - if I can!

I hope this helps again. And good luck in getting a permanent job!

Gareth

David Vollmer wrote:

Yes, I am not very fond of LINKS either. I took your suggestion and moved the
lookup workbook to what is mapped on our computers as Z:\Scorecard Lookups. I
am seriously thinking about putting the other sheet (the FORM) there as well.
It seems to make the most sense.

I didn't understand "UNC path" and I am not sure how to get to and use
Network Neighborhood. Do I need to call our network guru or is it something I
can do myself?

I have noticed that one of my macros, the SaveAsCopy one, doesn't allow me
to choose WHERE I want to save the workbook. It appears to save it in the
last used directory. Is there some code I can put in the macro to prompt for
the directory when I run it?

The macro code I use for that is:

Sub SaveAsCustomerName()

On Error GoTo ErrorHandler:
'
' SaveAsCustomerName Macro
' Macro recorded 9/6/2005 by David Vollmer
'
ActiveWorkbook.SaveCopyAs Filename:=Range("C51").Value _
'
ErrorHandler:

End Sub

The reference to C51 is where I have the customer name-number.xls located.

I will work on the spellcheck method you provided probably this weekend. I
have typed the ErrorHandler code in one of my macros - Thanks. Still thinking
about the myToolBar routine. Right now the command buttons on the form seem
to be doing the job. But I will experiment with your toolbar code and see if
I can make my form more professional. This is a huge bank I am doing this for
and at present we are all temporary employees hoping to be hired sometime
next year!

Again, your help is most appreciated. I see that you are very active in this
discussion group and have helped many people. That's great!


"Gareth" wrote:



Hi David,

I'm glad to be of assistance.

(1) Error Trapping
Do you mean trap so you can see what's going on? In which case just
click on DeBug - or set a break marker in your code by selecting the
line and pressing F9.

Or do you mean that you just want the macro to stop dead whenever it
encounters an error? Something like:

Sub Foo()

On Error Goto ErrorHandler:

'insert all your code here

ErrorHandler:

End Sub

But you might want to comment out the GoTo line until you've finished
writing your code otherwise you won't know what errors are occurring to
fix them!

Error handling is more sophisticated than just ignoring it though. See
"On Error Statement" in Help for more details. Note how in my COmmandBar
load function I use Error Trapping as an easy way to see if the
commandbar already exists.


(2) Spell Checking
You need to trap a change to your worksheet. Place the following code in
the code module for the worksheet you need to spellcheck on.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim rngToCheck As Range

'I don't know what cells you need to spellcheck, let's say these 3
Set rngToCheck = Union(Me.Range("E6:F6"), Me.Range("G5"))

'Check whether any of the changed cell(s) are ones we care about
Set rngToCheck = Intersect(Target, rngToCheck)
'if any of the changed range was in the range we needed to check
'go through them
If Not rngToCheck Is Nothing Then
rngToCheck.CheckSpelling IgnoreUppercase:=False, _
AlwaysSuggest:=True
Set rngToCheck = Nothing
End If

End Sub

NOTE: I tried this and it kept asking me whether I wanted to check the
rest of the sheet. Switching of Application.DisplayAlerts just meant it
checked them without me asking. I looked on the newsgroup and saw that
if you include the SpellLang argument then it resolves it e.g.
rngToCheck.CheckSpelling IgnoreUppercase:=False, _
AlwaysSuggest:=True, _
SpellLang:=1033
It does resolve it. I removed the SpellLang argument and it's no longer
asking so... you may like to add it.


(3) LINKS
I hate links! Um, I think you have two options he

(a) Always keep the two workbooks together. By this I mean that if you
have them both in c:\monkey\ and one references the other. If you move
them to c:\dog\ the references will still be valid.

(b) Place the WOrkbook that you need to reference on a network drive and
never move it. When you create the reference in your "form" workbook
reference it to the other. It will always remember.
NOTE: If you are using drive mappings, when referencing the other
workbook, make sure you have opened it via its UNC path NOT a mapped
drive i.e. go and find it in network neighborhood. Otherwise, if a user
has different drive mapping then it won't work.

Of course you could always use a macro to hunt down the workbook - but
that could take a while to scour all the available drives.

Have I understood your query correctly? I'm assuming that there's one
Reference Workbook (that everyone uses) and one Form style WOrkbook that
everyone has a copy of (although that could be on a network too and
jointly used). When you SaveCopyAs the copies will still link to the
Reference Workbook ok.

HTH,
Gareth

Hmmm - I thought it would have checked. Ah well...

Try looping until the file doesn't exist or until user agrees to
overwrite e.g.

Sub SaveMe()

Dim mySaveName As Variant
Dim blnOKToSave As Boolean

mySaveName = "c:\temp\" & Range("C51").Value

'loop until we get a name we're happy with
Do While Not blnOKToSave

mySaveName = Application.GetSaveAsFilename( _
InitialFileName:=mySaveName, _
FileFilter:="Excel Files (*.xls), *.xls")

'check to see if user hit cancel
If mySaveName = False Then Exit Sub

'Check to see if file already exists
If Dir(mySaveName) < "" Then
'it does - check user wants to overwrite
If MsgBox(mySaveName & vbCrLf & vbCrLf _
& "already exists. Overwrite?", _
vbYesNo) = vbYes _
Then blnOKToSave = True
Else
blnOKToSave = True
End If

Loop

ActiveWorkbook.SaveCopyAs Filename:=mySaveName

End Sub

David Vollmer wrote:
Sorry, Gareth, I noticed the missing code and felt really stupid! I corrected
it using ActiveWorkbook and it works fine. If, however, there is an existing
workbook with the same name in the chosen directory it will overwrite it
without giving me a warning. I have searched the discussion group for a way
to have the option of overwriting or changing the name to prevent that from
happening but have not been successful yet.

Thanks again!
David

"Gareth" wrote:


:-)

I didn't write the line to save the file - thought I'd leave that for
you - depending on how you wanted to save the file!

I've written it in below

Sub SaveMeAsCustomerName()
Dim mySaveName As Variant

mySaveName = Application.GetSaveAsFilename( _
InitialFileName:=Range("C51").Value, _
FileFilter:="Excel Files (*.xls), *.xls")

If mySaveName < False Then
'save your file!
'NEW LINE BELOW (change ThisWorkbook to ActiveWorkbook or
'other workbook as you see fit.
Thisworkbook.SaveCopyAs FileName:=mySaveName
End If

End Sub

cya,
Gareth


David Vollmer wrote:

Gareth,

I can't get the SaveMe code to work.
What I have entered is:

Sub SaveMeAsCustomerName()
Dim mySaveName As Variant

mySaveName = Application.GetSaveAsFilename( _
InitialFileName:=Range("C51").Value, _
FileFilter:="Excel Files (*.xls), *.xls")

If mySaveName < False Then
'save your file!
End If

End Sub

It appears to work but then I can not find the "saved file" anywhere. What
have I done wrong? It doesn't seem to make any difference what directory I
choose or whether I change the C51 name.

Thank you!


"Gareth" wrote:



Hi David,

- UNC PATHS -
By using a UNC path you're specifying directly where on the network a
file you should be saved - rather than relying on the drive mapping. I
prefer this (it's a personal thing) because it means if the drive
mapping changes - there'll be no problems.

For example, your "Z:\Scorecard Lookups" directory could be accessed by
something like:

"\\servername\shareddata\Scorecard Lookups" - thus this path is drive
mapping agnostic.

If you're ever using VBA to access a network file I would highly
recommend using this method of addressing it. Note that links (and
indeed VBA project references) have a nasty habit of reverting to drive
mapped locations. By this I mean, if you open a workbook by accessing it
via the mapped drive it often surreptitiously changes the link to
"z:\etc etc." even if it was "\\server\etc." originally. That can be a
headache.

All this said, if you're reasonably confident that mapping won't change
(too often) and workbooks won't be copied locally and remain used on the
network you shouldn't be too preoccupied by all this.

There is pre-written VBA code (search this NG for getUNC or something)
that returns the UNC path from a mapped drive. But I don't think you
need it here.


- SAVECOPYAS -
You specify the location by stating the full path in the Filename
argument of SaveCopyAs. e.g.
ActiveWorkbook.SaveCopyAs Filename:="c:\temp\" & Range("C51").Value

If you want to save in the same location as the original, try something
like:

With ActiveWorkbook
.SaveCopyAs Filename:= .Path & "\" & Range("C51").Value
End with
(I'm using the With/End With just to keep things tidy - it's not necessary.)

Should you want to allow the user to choose a folder to save it in, a
simpler way to achieve this is using Application.GetSaveAsFilename

In your case, do something like

Sub SaveMe()
Dim mySaveName As Variant

mySaveName = Application.GetSaveAsFilename( _
InitialFileName:=Range("C51").Value, _
FileFilter:="Excel Files (*.xls), *.xls")

If mySaveName < False Then
'save your file!
End If

End Sub

Note, you could also specify the original save directory above e.g.
.... InitialFileName:="c:\temp\" & Range("C51").Value
Should make less browsing for the user. Obviously, if they choose a
different folder you could always remember it and, next time they save,
start off in that folder.

It is of course also possible to allow them to choose the save folder
without allowing them to change the filename. Either by simply stripping
out the filename from mySaveName and just using the path they chose or
by giving them a dedicated folder select dialogue box - but that is more
complicated because you need APIs.

- BUTTONS -
I like command bars (still hate links!). IMHO I wouldn't, however, say
they're really that useful here. You don't really need want to occupy
too much of the user's screen real estate and to be tidy, you should
hide or delete them when you close or deactivate your workbook - that's
a bit of fathing around for no real gain in this instance. (I prefer to
use them when I use an AddIn.)


Thanks for your kind words - I'm not really that active here. There are
plenty of real regulars. I just tend to whizz through and see who hasn't
been answered for a while and help them - if I can!

I hope this helps again. And good luck in getting a permanent job!

Gareth

David Vollmer wrote:


Yes, I am not very fond of LINKS either. I took your suggestion and moved the
lookup workbook to what is mapped on our computers as Z:\Scorecard Lookups. I
am seriously thinking about putting the other sheet (the FORM) there as well.
It seems to make the most sense.

I didn't understand "UNC path" and I am not sure how to get to and use
Network Neighborhood. Do I need to call our network guru or is it something I
can do myself?

I have noticed that one of my macros, the SaveAsCopy one, doesn't allow me
to choose WHERE I want to save the workbook. It appears to save it in the
last used directory. Is there some code I can put in the macro to prompt for
the directory when I run it?

The macro code I use for that is:

Sub SaveAsCustomerName()

On Error GoTo ErrorHandler:
'
' SaveAsCustomerName Macro
' Macro recorded 9/6/2005 by David Vollmer
'
ActiveWorkbook.SaveCopyAs Filename:=Range("C51").Value _
'
ErrorHandler:

End Sub

The reference to C51 is where I have the customer name-number.xls located.

I will work on the spellcheck method you provided probably this weekend. I
have typed the ErrorHandler code in one of my macros - Thanks. Still thinking
about the myToolBar routine. Right now the command buttons on the form seem
to be doing the job. But I will experiment with your toolbar code and see if
I can make my form more professional. This is a huge bank I am doing this for
and at present we are all temporary employees hoping to be hired sometime
next year!

Again, your help is most appreciated. I see that you are very active in this
discussion group and have helped many people. That's great!


"Gareth" wrote:




Hi David,

I'm glad to be of assistance.

(1) Error Trapping
Do you mean trap so you can see what's going on? In which case just
click on DeBug - or set a break marker in your code by selecting the
line and pressing F9.

Or do you mean that you just want the macro to stop dead whenever it
encounters an error? Something like:

Sub Foo()

On Error Goto ErrorHandler:

'insert all your code here

ErrorHandler:

End Sub

But you might want to comment out the GoTo line until you've finished
writing your code otherwise you won't know what errors are occurring to
fix them!

Error handling is more sophisticated than just ignoring it though. See
"On Error Statement" in Help for more details. Note how in my COmmandBar
load function I use Error Trapping as an easy way to see if the
commandbar already exists.


(2) Spell Checking
You need to trap a change to your worksheet. Place the following code in
the code module for the worksheet you need to spellcheck on.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim rngToCheck As Range

'I don't know what cells you need to spellcheck, let's say these 3
Set rngToCheck = Union(Me.Range("E6:F6"), Me.Range("G5"))

'Check whether any of the changed cell(s) are ones we care about
Set rngToCheck = Intersect(Target, rngToCheck)
'if any of the changed range was in the range we needed to check
'go through them
If Not rngToCheck Is Nothing Then
rngToCheck.CheckSpelling IgnoreUppercase:=False, _
AlwaysSuggest:=True
Set rngToCheck = Nothing
End If

End Sub

NOTE: I tried this and it kept asking me whether I wanted to check the
rest of the sheet. Switching of Application.DisplayAlerts just meant it
checked them without me asking. I looked on the newsgroup and saw that
if you include the SpellLang argument then it resolves it e.g.
rngToCheck.CheckSpelling IgnoreUppercase:=False, _
AlwaysSuggest:=True, _
SpellLang:=1033
It does resolve it. I removed the SpellLang argument and it's no longer
asking so... you may like to add it.


(3) LINKS
I hate links! Um, I think you have two options he

(a) Always keep the two workbooks together. By this I mean that if you
have them both in c:\monkey\ and one references the other. If you move
them to c:\dog\ the references will still be valid.

(b) Place the WOrkbook that you need to reference on a network drive and
never move it. When you create the reference in your "form" workbook
reference it to the other. It will always remember.
NOTE: If you are using drive mappings, when referencing the other
workbook, make sure you have opened it via its UNC path NOT a mapped
drive i.e. go and find it in network neighborhood. Otherwise, if a user
has different drive mapping then it won't work.

Of course you could always use a macro to hunt down the workbook - but
that could take a while to scour all the available drives.

Have I understood your query correctly? I'm assuming that there's one
Reference Workbook (that everyone uses) and one Form style WOrkbook that
everyone has a copy of (although that could be on a network too and
jointly used). When you SaveCopyAs the copies will still link to the
Reference Workbook ok.

HTH,
Gareth

Thank you, sir!

David



"Gareth" wrote:

Hmmm - I thought it would have checked. Ah well...

Try looping until the file doesn't exist or until user agrees to
overwrite e.g.

Sub SaveMe()

Dim mySaveName As Variant
Dim blnOKToSave As Boolean

mySaveName = "c:\temp\" & Range("C51").Value

'loop until we get a name we're happy with
Do While Not blnOKToSave

mySaveName = Application.GetSaveAsFilename( _
InitialFileName:=mySaveName, _
FileFilter:="Excel Files (*.xls), *.xls")

'check to see if user hit cancel
If mySaveName = False Then Exit Sub

'Check to see if file already exists
If Dir(mySaveName) < "" Then
'it does - check user wants to overwrite
If MsgBox(mySaveName & vbCrLf & vbCrLf _
& "already exists. Overwrite?", _
vbYesNo) = vbYes _
Then blnOKToSave = True
Else
blnOKToSave = True
End If

Loop

ActiveWorkbook.SaveCopyAs Filename:=mySaveName

End Sub

David Vollmer wrote:
Sorry, Gareth, I noticed the missing code and felt really stupid! I corrected
it using ActiveWorkbook and it works fine. If, however, there is an existing
workbook with the same name in the chosen directory it will overwrite it
without giving me a warning. I have searched the discussion group for a way
to have the option of overwriting or changing the name to prevent that from
happening but have not been successful yet.

Thanks again!
David

"Gareth" wrote:


:-)

I didn't write the line to save the file - thought I'd leave that for
you - depending on how you wanted to save the file!

I've written it in below

Sub SaveMeAsCustomerName()
Dim mySaveName As Variant

mySaveName = Application.GetSaveAsFilename( _
InitialFileName:=Range("C51").Value, _
FileFilter:="Excel Files (*.xls), *.xls")

If mySaveName < False Then
'save your file!
'NEW LINE BELOW (change ThisWorkbook to ActiveWorkbook or
'other workbook as you see fit.
Thisworkbook.SaveCopyAs FileName:=mySaveName
End If

End Sub

cya,
Gareth


David Vollmer wrote:

Gareth,

I can't get the SaveMe code to work.
What I have entered is:

Sub SaveMeAsCustomerName()
Dim mySaveName As Variant

mySaveName = Application.GetSaveAsFilename( _
InitialFileName:=Range("C51").Value, _
FileFilter:="Excel Files (*.xls), *.xls")

If mySaveName < False Then
'save your file!
End If

End Sub

It appears to work but then I can not find the "saved file" anywhere. What
have I done wrong? It doesn't seem to make any difference what directory I
choose or whether I change the C51 name.

Thank you!


"Gareth" wrote:



Hi David,

- UNC PATHS -
By using a UNC path you're specifying directly where on the network a
file you should be saved - rather than relying on the drive mapping. I
prefer this (it's a personal thing) because it means if the drive
mapping changes - there'll be no problems.

For example, your "Z:\Scorecard Lookups" directory could be accessed by
something like:

"\\servername\shareddata\Scorecard Lookups" - thus this path is drive
mapping agnostic.

If you're ever using VBA to access a network file I would highly
recommend using this method of addressing it. Note that links (and
indeed VBA project references) have a nasty habit of reverting to drive
mapped locations. By this I mean, if you open a workbook by accessing it
via the mapped drive it often surreptitiously changes the link to
"z:\etc etc." even if it was "\\server\etc." originally. That can be a
headache.

All this said, if you're reasonably confident that mapping won't change
(too often) and workbooks won't be copied locally and remain used on the
network you shouldn't be too preoccupied by all this.

There is pre-written VBA code (search this NG for getUNC or something)
that returns the UNC path from a mapped drive. But I don't think you
need it here.


- SAVECOPYAS -
You specify the location by stating the full path in the Filename
argument of SaveCopyAs. e.g.
ActiveWorkbook.SaveCopyAs Filename:="c:\temp\" & Range("C51").Value

If you want to save in the same location as the original, try something
like:

With ActiveWorkbook
.SaveCopyAs Filename:= .Path & "\" & Range("C51").Value
End with
(I'm using the With/End With just to keep things tidy - it's not necessary.)

Should you want to allow the user to choose a folder to save it in, a
simpler way to achieve this is using Application.GetSaveAsFilename

In your case, do something like

Sub SaveMe()
Dim mySaveName As Variant

mySaveName = Application.GetSaveAsFilename( _
InitialFileName:=Range("C51").Value, _
FileFilter:="Excel Files (*.xls), *.xls")

If mySaveName < False Then
'save your file!
End If

End Sub

Note, you could also specify the original save directory above e.g.
.... InitialFileName:="c:\temp\" & Range("C51").Value
Should make less browsing for the user. Obviously, if they choose a
different folder you could always remember it and, next time they save,
start off in that folder.

It is of course also possible to allow them to choose the save folder
without allowing them to change the filename. Either by simply stripping
out the filename from mySaveName and just using the path they chose or
by giving them a dedicated folder select dialogue box - but that is more
complicated because you need APIs.

- BUTTONS -
I like command bars (still hate links!). IMHO I wouldn't, however, say
they're really that useful here. You don't really need want to occupy
too much of the user's screen real estate and to be tidy, you should
hide or delete them when you close or deactivate your workbook - that's
a bit of fathing around for no real gain in this instance. (I prefer to
use them when I use an AddIn.)


Thanks for your kind words - I'm not really that active here. There are
plenty of real regulars. I just tend to whizz through and see who hasn't
been answered for a while and help them - if I can!

I hope this helps again. And good luck in getting a permanent job!

Gareth

David Vollmer wrote:


Yes, I am not very fond of LINKS either. I took your suggestion and moved the
lookup workbook to what is mapped on our computers as Z:\Scorecard Lookups. I
am seriously thinking about putting the other sheet (the FORM) there as well.
It seems to make the most sense.

I didn't understand "UNC path" and I am not sure how to get to and use
Network Neighborhood. Do I need to call our network guru or is it something I
can do myself?

I have noticed that one of my macros, the SaveAsCopy one, doesn't allow me
to choose WHERE I want to save the workbook. It appears to save it in the
last used directory. Is there some code I can put in the macro to prompt for
the directory when I run it?

The macro code I use for that is:

Sub SaveAsCustomerName()

On Error GoTo ErrorHandler:
'
' SaveAsCustomerName Macro
' Macro recorded 9/6/2005 by David Vollmer
'
ActiveWorkbook.SaveCopyAs Filename:=Range("C51").Value _
'
ErrorHandler:

End Sub

The reference to C51 is where I have the customer name-number.xls located.

I will work on the spellcheck method you provided probably this weekend. I
have typed the ErrorHandler code in one of my macros - Thanks. Still thinking
about the myToolBar routine. Right now the command buttons on the form seem
to be doing the job. But I will experiment with your toolbar code and see if
I can make my form more professional. This is a huge bank I am doing this for
and at present we are all temporary employees hoping to be hired sometime
next year!

Again, your help is most appreciated. I see that you are very active in this
discussion group and have helped many people. That's great!


"Gareth" wrote:




Hi David,

I'm glad to be of assistance.

(1) Error Trapping
Do you mean trap so you can see what's going on? In which case just
click on DeBug - or set a break marker in your code by selecting the
line and pressing F9.

Or do you mean that you just want the macro to stop dead whenever it
encounters an error? Something like:

Sub Foo()

On Error Goto ErrorHandler:

'insert all your code here

ErrorHandler:

End Sub

But you might want to comment out the GoTo line until you've finished
writing your code otherwise you won't know what errors are occurring to
fix them!

Error handling is more sophisticated than just ignoring it though. See
"On Error Statement" in Help for more details. Note how in my COmmandBar
load function I use Error Trapping as an easy way to see if the
commandbar already exists.


(2) Spell Checking
You need to trap a change to your worksheet. Place the following code in
the code module for the worksheet you need to spellcheck on.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim rngToCheck As Range

'I don't know what cells you need to spellcheck, let's say these 3
Set rngToCheck = Union(Me.Range("E6:F6"), Me.Range("G5"))

'Check whether any of the changed cell(s) are ones we care about
Set rngToCheck = Intersect(Target, rngToCheck)
'if any of the changed range was in the range we needed to check
'go through them

You're most welcome mate.

David Vollmer wrote:
Thank you, sir!

David