Replies

Sam,
I tried the sample macro you posted and it failed. To get it to work on my Excel (97) I had to remark out the last part of the Dim statement (see below).
Dim Rsp ‘As VbMsgBoxResult

Maybe you developed under higher level Excel.
It is a handy macro to build on.

I just love this site for all the information that is available here.

Chuck

Unless I misunderstand the question, the macro below (from Legare Coleman) will do the job.
The worksheet protection does not have to turned on to work.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
‘ Source Woody’s Lounge Author: Legare Coleman
‘ This prevents user from changing the cells without using
‘ protection and locking worksheet.

If Intersect(Target, ActiveSheet.Range(“B80:D84”)) Is Nothing Then Exit Sub
Application.EnableEvents = False
MsgBox “You can’t alter this cell.”, vbExclamation + vbOKOnly
Application.Undo
Application.EnableEvents = True
End Sub

Please attach file.

Chuck

I set it up both ways (nestred IF and INDIRECT). Using the Nested IF, the user can erase the contents of G2 (State) and the drop down list for City (I2) lets user select a blank entry from the drop down list. If you set it up with INDIRECT and erase the contents of G2 (State), the user cannot access the drop down list for City in I2. A small point but may be important to designer.
Chuck

Tony,
In the past I have used macros to print. Create one macro that will print the three sheets and a different macro to print the 6 sheets.
Hope this helps.

Chuck

Legare,

What is the oleApp (in your macro) for? I tested my macro and it worked without using oleApp.
Just curious…

Thanks,

Chuck

I use a macro to open a password protected workbook.
Code looks like:
Workbooks.Open FileName:=”METC_MEARSDATAUSERSREIMERCREXCELDelimitTest2.xls”, Password:=”t3″, WriteResPassword:=”t3″
Try it this way.
Good luck,

Chuck

When you save macros in your Personal.xls and then close Excel, you should be asked if you want to save the changes to Personal.xls. Respond yes.
Or you might try saving the Personal.xls as soon as the macros are done being written:
Go to the Visual Basic Editor (press Alt+F11 if it is not running).
The menu at the top will let you save Personal.xls.
If these two methods fail to save the macros in Personal.xls, I would suspect you do not have Excel installed locally on your PC, and there may be problems with rights to the Personal.xls file that is stored on the network.

Chuck

You should be able to associate the extension .vzg with Excel. I created a sample file and right-clicked it -then selected Open With, scrolled down to Excel and clicked the box Always use.
Now when you double click a file with that extension, it will start Excel and load the file you double-clicked.
If you need the macros loaded at the same time, just put the macros in your Personal.xls file (it always opens when Excel does).
If I understood your question, this should do it. I am using Excel 97 on an NT system.
Good luck,
Chuck

I do not have Excel XP.
In Excel 97, I use the Edit / Move or Copy Sheet method. It might work in your version too.
It gets all formatting (including column widths)
From the menu select Edit
Select Move or Copy Sheet
In the dialog box indicate the workbook you want it copied to (or select New)
Be sure you put check mark in box for Create Copy
Click OK button
Then just rename the worksheet tab and erase the old data that you do not want.
Good luck.

Chuck

Brooke,

Not only fast but accurate! It worked so fast I almost did not realize that it accomplished the task. I thank you.
I just wish it looked more complicated That was embarrasingly simple. (and I spent about an hour on it and kept failing)
I LOVE THIS PLACE!

Chuck

Not in Excel 97. Color tabs are not provided for.

Chuck

Set column A to the desired width and format the cells in column A to alignment/wrap text. Your last entry is very long and using autofit column width will probably not give you a disired result. If you put ‘ in the empty cells in column B you will not be able to read the ends of the longer text entries in column B.

Hope this is of some help,

Chuck

Another option is a macro that counts the unique items in a named range. I am attaching a sample workbook. You can modify the macros to do something based on whether you wish to continue or no. This example is based on a tip by J.G.Hussey, published in “Visual Basic Programmer’s Journal”
Just another option….

Chuck

Could you attach a sample workbook showing the types of text entries you wish converted?

Chuck