PDA

View Full Version : Solved: sheet activate formula problem?



thekneeguy
10-15-2005, 07:11 PM
Hello all....

I have a workbook that opens to a navigational sort of sheet. Then the user selects a command button with an assigned macro to go to the main sheet.

The macro is essentially

sheet("products").activate
sheet("products").visible = true
range("b13").activate

so that when the sheet opens it automatically goes to the first appropriate row of the sheet. This row then has two columns for items and cost. The next column the user enters the number of those items they select and a hidden column multiplies the number items by the cost and deducts it from a "beginning budget" cell which shows a running sum of remaining budget.

My problem is this...for some reason the first row will NOT retain the formulas. When I unhide the columns and replace the formula in the first row it does not stay when the sheet is activated which leads me to think maybe there is something wrong with the code to activate the sheet?

If so, is there a different way you would suggest coding this or a workaround of some sort?

The sheet is made to autopopulate the first column of the first available row no matter where the cursor is the only reason i activated a cell was that the picture elements at the top of the page were always highlighted when the sheet opened and i wanted to get away from that. But I still don't understand why the row won't retain the formula. Any ideas anyone?

Thank you

Norie
10-15-2005, 07:35 PM
Do you have any other code?

thekneeguy
10-15-2005, 07:40 PM
there is a great deal of code but the only other code that pertains to the rows or columns is that when a user selects from a list box on a user form the selection is automatically populated into the first column and the first available row.

I re coded the sheet activate so that it activated a row above the rows used for entry and it works fine. weird. well at least i have a workaround. i will investigate the rest later i guess.

thank you very much

johnske
10-15-2005, 10:28 PM
...so that when the sheet opens it automatically goes to the first appropriate row of the sheet...Without seeing your code, perhaps this is the reason... Most formulas to select the first available row to make entries in are of the form (last row of the worksheet).End(xlUp).Offset(1, 0).

Because of the Offset(1, 0) the second row on the worksheet is chosen. So the 1st row is conventionally reserved for headings and this requires a workaround when there are no headings.

HTH,
John :)

thekneeguy
10-17-2005, 07:52 AM
That makes sense! I will play with that thank you!