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.