SOLVED

Sheets

%3CLINGO-SUB%20id%3D%22lingo-sub-2319755%22%20slang%3D%22en-US%22%3ESheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2319755%22%20slang%3D%22en-US%22%3E%3CP%3ETrouble%20getting%20sheet%20to%20move%20to%20next%20page%20and%20to%20the%20previous%20sheet%20using%20buttons%20in%20Excel%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2319755%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2321106%22%20slang%3D%22en-US%22%3ERe%3A%20Sheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2321106%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1044169%22%20target%3D%22_blank%22%3E%40tnvolsfan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWhen%20you%20click%20one%20of%20the%20buttons%2C%20it%20changes%20the%20value%20of%20cell%20B3%20on%20the%20Schedule%20sheet.%3C%2FP%3E%0A%3CP%3EThis%20causes%20the%20Worksheet_Change%20event%20procedure%20to%20run%2C%20and%20this%20calls%20ScheduleRefresh.%3C%2FP%3E%0A%3CP%3EThe%20ScheduleRefresh%20macro%20has%20a%20loop%20For%20WeekCol%20%3D%205%20To%2023%20Step%203%20...%20Next%20WeekCol.%3C%2FP%3E%0A%3CP%3EThe%20second%20time%20through%20this%20loop%2C%20WeekCol%20equals%208.%3C%2FP%3E%0A%3CP%3EThe%20code%20adds%20a%20new%20sheet%2C%20and%20tries%20to%20rename%20it%20to%20the%20value%20of%20Cells(110%2C%20WeekCol).%20But%20Cells(110%2C%20%3CLI-EMOJI%20id%3D%22lia_smiling-face-with-sunglasses%22%20title%3D%22%3Asmiling_face_with_sunglasses%3A%22%3E%3C%2FLI-EMOJI%3E%20i.e.%20cell%20H110%20is%20empty.%20This%20causes%20an%20error.%3C%2FP%3E%0A%3CP%3EPerhaps%20some%20of%20these%20steps%20shouldn't%20occur%20-%20can%20you%20explain%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2321890%22%20slang%3D%22en-US%22%3ERe%3A%20Sheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2321890%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1044169%22%20target%3D%22_blank%22%3E%40tnvolsfan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIt%20looks%20like%20the%20code%20and%20sheet%20layout%20don't%20match%20anymore.%20I%20suspect%20that%20extra%20columns%20have%20been%20inserted%20for%20the%20first%20day%20(columns%20G%2C%20H%20and%20I).%20Therefore%2C%20that%20day%20does%20not%20conform%20to%20the%20regular%20structure%20that%20the%20code%20expects.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Trouble getting sheet to move to next page and to the previous sheet using buttons in Excel

10 Replies

@tnvolsfan 

When you click one of the buttons, it changes the value of cell B3 on the Schedule sheet.

This causes the Worksheet_Change event procedure to run, and this calls ScheduleRefresh.

The ScheduleRefresh macro has a loop For WeekCol = 5 To 23 Step 3 ... Next WeekCol.

The second time through this loop, WeekCol equals 8.

The code adds a new sheet, and tries to rename it to the value of Cells(110, WeekCol). But Cells(110, 8), i.e. cell H110, is empty. This causes an error.

Perhaps some of these steps shouldn't occur - can you explain?

@Hans Vogelaar 

This excel program was downloaded from Excel freelancers website and I really like how it works. I really don't know why this happens and I try to find the root cause of it. If there anything in vba code that doesn't need to be there, can you suggest any changes to it that will as normal? Thanks.

 

Greg

@tnvolsfan 

It looks like the code and sheet layout don't match anymore. I suspect that extra columns have been inserted for the first day (columns G, H and I). Therefore, that day does not conform to the regular structure that the code expects.

Oh, I see it now. I did add those columns H, G, I. How can I include those columns in the macro code?
Greg

@tnvolsfan 

Do you want those extra columns for all days of the week, or just for the first day?

All days of the week please

@tnvolsfan 

That will require a MAJOR rewrite of the code. Stay tuned.

Ok. Geeze. I didn't mean for it to be that bad. I'm sorry causing all this extra work.
best response confirmed by tnvolsfan (Occasional Contributor)
Solution

@tnvolsfan 

See if this works for you.

 

Yes! That works for me! I appreciate your time! Again, thanks!!