formulas with hidden columns row activate problem?

[striveforexcelence]

I have a workbook that has an initial sheet on open and then when an assigned macro on a navigation bar is selected another sheet is activated and becomes visible and range "B9" is activated so that the first entry will appear in the first appropriate row for that sheet.

The sheet has three column heading cells for beginning budgets for the months of january, february and march. When a user selects an item on below rows in column b the item cost is autopopulated into column c and the user then selects the number of that item for each month. The item cost is multiplied by the number of units chosen and stored in a hidden colum which totals at the bottom of the hidden column. That hidden total is subtracted from the beginning january budget to show a running sum total of the beginning budget minus january selected items.



Jan Budget Feb Budget Mar Budget
$3500 $3500 $3500

Item Cost # for Jan #for Feb # for Mar
Computer $700 1 2 1

Jan Budget then changes to $2800 and Feb $2100 etc.

This works fine for any rows after the first row. The first row does not hold the formula. When I unhide the three columns, the correct forumal appears in the second and consecutive rows for those columns but the first row will not maintain the formula despite my re-entering it??? Does this have something to do with the row being activated on sheet open? Is there something that I can add to the code to make it work around this?

Also, when a number is entered into the column to indicate the number of units for the selection, the arrow keys do not work so that the user can just arrow over to the next month and enter a number for that item???

Does anyone have any idea what might be causing these two things?

Thank you for any information.

 

[WalterContrata]

If it is not too much trouble, can I get you to post the code, or the relevant parts of it?

As for the arrow keys problem, when some of my macros are running, Excel will not respond to short cuts like Ctl-v, in spite of the DoEvents command. I'm not sure why, but I'll have a look at it when I return to the office.

Best Regards,
Walter

 

[striveforexcelence]

thanks i was really stupid about the arrow keys lol the scroll lock button was off but the rest is really odd


As for the code Im not really sure what you want to see but mainly you come to the opening sheet and you click on a hyperlink button that opens a sign on form for a text box password (this is just a demo program so the password is just anything to demonstrate it). Then when you click the ok button on the signon form it activates the sheet and the first column of the first appropriate row

Private Sub CommandButton300_Click()
If UCase("TextBox1.Value") >= "" Then SignOn.Hide
TextBox1.Value = ""


Sheets("Forecast").Activate
Sheets("Forecast").Visible = True
Sheets("Forecast").Range("B13").Activate

End Sub


Thanks for looking at this

 

[WalterContrata]

Good job eliminating the arrow key problem! I don't know whether I can help with the rest, but it sounds interesting, and I'd like to give it a try.

The sign-on routine makes sense. After that, is it all worksheet?

Walter

 

[WalterContrata]

Does the "Forecast" worksheet look like this?
[tt]
A B C D E F G
1 Jan Feb
2 2640 1920
3
4 Item Cst Jan Feb
5 Comp 700 1 2 700 1400
6 Paper 10 4 6 40 60
7 Pens 8 15 15 120 120
8
9 Total 860 1580
[/tt]

 

[WalterContrata]

In the previous, I omitted Mar. Columns F and G are supposed to be hidden columns that contain the total cost for each item for Jan and Feb, respectively. In row 9, those data are summed, and the result is subtracted from the budget in row 2. Is yours something like that?

What does the user to select, and how does the cost get autopopulated in column C?

Which row do you mean and which formula, when you write that
"The first row does not hold the formula".

Best Regards,
Walter

 

[striveforexcelence]

yes it does look like that... i re did the activate sheet code to a different row cell and it works fine. weird. something about if you activate a cell in a row the row loses the formulas.

This project has been quite a pain it is a demo for a us/canada/south america roll out and it has an audiovisual tutorial which i couldnt record until I fixed that crazy little problem. Thanks for helping. Someday I will investigate that further.

 

[WalterContrata]

No thanks to me, but I'm glad you fixed it. Sounds like a big project.

Best Regards,
Walter

 

[striveforexcelence]

well, I thought I had it fixed by activating a non formulaed (?) row on sheet activation. for some reason the first row will not maintain a formula??? Any ideas?

 

[WalterContrata]

How frustrating. As I understand it, the formulae in a row of the worksheet are somehow getting changed when the macro runs. I would try using the VB debugger to figure out at what macro line the change happens. Once you know that, the problem should be easier to solve.

In the VB editor I would set a watch to break the program when the value or formula a cell in that row.

Right click on code window -> Add Watch.

In the expression window, enter "Worksheets("Forecast").Range("B2").Value" or ("Forecast").Range("B2").Formula", where "B2" should be replaced by one of the cells that is giving you trouble.

Choose "All Modules" and "All Procedures".

Click the "Break when value changes" radio button.

Click "OK".

Now run the macro. Execution should halt after the cell in question changes. The next line of code will be highlighted in yellow. See if you can figure out how the cell is getting changed.

Being new around here, I don't know the ettiquite, but maybe it would be good to repost by Monday morning. I'm afraid that this thread has become so long that people may pass it up. But it is interesting, and some of the pros may have the answer at their fingertips.

I hope this helps,

Walter