Search results for query: *

How to re-link formulas from workbooks which have been renamed and contain broken links to the current or a new workbook, assuming it is just the link which is broken but the reference sheets still exist in the workbook. Clearly this can be done using the menus but if you dont want to user to...

Thanks Strongm. OS is XP Pro. 2002 SP3 Path is the documents and settings for that user files then excel. Thats a great help though, I have disabled the autorecover function as it is not needed for this application. Thanks. HG

Hello I have a program which has been running on excel 2003 and has been migrated to 2007. Most things are working fine except. When the program is left open for a while it returns the following error. Microsoft excel canot find the following file "path\~ar28CE.xar" When the user clicks OK a...

Thanks for all the comments, indeed events, screenupdating etc are toggled but that is done in the calling routine. I tried do loop and for next and there is not that much difference at least its not noticable. Maybe it needs to be a long loop before you notice the difference. Cheers HG

Hello, This is the new code, it work like a rocket. Thanks for all your hellp, simplicity is indeed the best answer. Sub bev_cogs_format() ' Formats the beverage COGS sheet for printing ' if the line has nothing in it in terms of weight then it is hidden...

The output is to be printed and is automated within the application. It prints batch sheets for our factories to use. So the rows have to be hidden in order to get a good looking format. there is data underneath these rows which always needs to be present. In addition the sheet can have many...

Thanks for your inputs. Makin the changes suggested it was the same speed. The issue seems to be the processing of the line of code which hides the row. Anyway, I speeded up by hiding everything then unhiding the rows with data, as there are less of them. I tried for next, do while I also...

The worksheet has a grid containing raw materials, in order to format the worksheet correctly the first cell = "." if there is data in the row otherwise it is blank. The following code then goes through each row, if the row is blank it is hidden. Other information, it is called from the...

This is helpful, thanks. I guess the only way to make this work is after every 5 changes of colours make the applicatin save automatically, that then resets the formats in the mind of Excel, if I udnerstand you well. I imagine nobody has found a VBA way to set the formats back to 0 in the mind...

What I have done in the past is give each user a password which is derived from an algoritim and their username that way only the user (who is logged in) with teh right password can use the workbook and it opens at the right worksheet while hidign the others. You need to have a module for...

I have an application to which I have added an option so the user can change the colours of the environment. There is a pallet down the side of the screen which is clickable and runs a proc. for changing the colours in the unlocked user entry cells of the application. I understand the maximum...

bugger! Thanks for setting me straight. HG

thanks steve, in fact they make sense to a chemist so thats OK. I wasn't sure if you dimmed many variables on the same line and then gave a variable class at the end i thought all the variables in that line then took that class e.g. dim a,b,c as string ' all dimmed as strings dim a,b as...

Thanks for taking the time anyway, I am pleased it is solved but a bit embarrassed that it was just typos. Cheers HG

I only have two eyes and as many brain cells, you are correct. Sorry to ask such a dumb question but the numbers were spinning around and around there for a minute. THANKS!!!! HG

If I understand correctly you want to be able to insert a worksheet name into a formula, although it is not clear what you are trying to do. Sub dump() Dim varwks As String varwks = Sheets("sheet1").Name Range("$B$1").Formula = "=" & varwks & "!$A$1*$A$2" End Sub you can store...

If you send the file to me I can try and look at it for you, two minds may be better than one in this case.

I have some code below which calculates the strength of an acid indexed against citric. However, when the code runs it does not give the right answer even though the algoritims are correct. If the same calculations are put into a spread sheet they work. here is the code and where the error...

I have a query in my worksheet. Normally when i right click on a worksheet I have code which allows me to customise the "cell" menu. This works fine everywhere. However, when i right click on the data which is the body of the query I cannot customise the "cell" menu anymore. I imagine it is...

Below are two procedures which work together to create a security key which locks a VBA Office based program to a users PC once it is run. If you want to ensure that VBA Office based software once installed on one PC cannot work on another PC then this will help. I just posted it as a thank...