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

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.

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