Code Skipping (XP)

Topic

New Reply

I have a challenge with a line of code that is quite simple: selecting a sheet within the workbook.

Example:
Sheets(“Main”).Select

When I step through or run the code the sheet is never selected.

Any ideas as to why? I tried rebooting (warm boot) and this did not resolve the issue.

Thanks,
John

Reply | Quote

Replies

Odd, works fine for me. Are you sure the active workbook (!) has a sheet named “Main”? You might need to specify what workbook you want activated first.

Reply | Quote

Check to see if you do not have a space before or after the word “Main” in the tab of the workbook. Maybe delete the worksheet name in the sheet tab and re-type “Main” again (just to eliminate any “hidden” characters, and then run the code again to see if it solves the problem.

Reply | Quote

Rudi,

I came across this post while looking for solution to my problem. I would like to have some code that would check the name on the worksheet tab and replace it after stripping any spaces out. This could be either just the active sheet or one that would cycle through all sheets.
Any ideas?

Thanks,

Reply | Quote

Like this? It operates on whichever sheets you have selected (easy enough to select all).

Sub SetSheetNameNoSpace()
Dim oSheet As Worksheet

Application.ScreenUpdating = False
For Each oSheet In ActiveWindow.SelectedSheets
With oSheet
If .ProtectContents = False Then _
.Name = Replace(.Name, ” “, “”)
End With
Next oSheet

Application.ScreenUpdating = True
End Sub

Reply | Quote

John,

Thanks! That will make my life much easier. For some reason, the users change the tab names by inserting spaces (even though they were told not to). This will take care of it.

Thanks again.

Reply | Quote

What is the issue with spaces in the names? They work just as well as names without spaces….

Steve

Reply | Quote

I was gonna say the same.

Except IIRC they make =INDIRECT() references a bit tricky.

Reply | Quote

If you want to avoid trouble with users renaming your worksheets, use the codename in your code instead of the tab name.

Reply | Quote

Further to Jan Karel’s comments on using Codename, see this thread, especially towards the end.

Reply | Quote

Steve, Jan and John

Sorry for the delay in responding – I took a long weekend -away from work.

Thanks for the replies and suggestions.
I have some code that cycles through all the sheets that have a chart embedded and updates the charts.
This worked great until it hit a sheet that had a space in the tabname. I am not sure why it died, but it did. It took forever to figure out what was causing it.
I as able to adjust the code to check for spaces in the tab name and remove them on the fly.
Works great now.

Thanks again (I really do LOVE this site).

Reply | Quote

Perhaps if you post the code lines which error out, we can find another solution.

Reply | Quote

John,

No doubt there are many diffrerent ways to do it!
I am attaching the txt file of the code that fails. At the bottom of the text file, I marked where it fails and what the error message is.
The workbook is too big to send, however if need be, I can chop it up and send a smaller section.
The workbook, as it is now, does work. But, you may have a better (faster, shorter, etc) method.
The sheets each have one chart. The macro goes through each sheet and updates the chart, based on the new data that has been entered into the table that is on the same sheet. The code that failed (becuase of a space in the tabname) was supposed to replace the datatable series that showed 1 2 3 4 etc, with Jan Feb Mar Apr (as shown in the table on the sheet).

Reply | Quote

If you change the line

MonthInfo = "=" & DaName & "!" & "R25C2:R25C13"

to

MonthInfo = "='" & DaName & "'!" & "R25C2:R25C13"

the rest of the code should be able to handle sheet names with spaces.

Reply | Quote

Hans,

Thanks. Maybe I should let them have spaces in the tab names….
I will modify the code as suggested.
Thanks
Thanks also for the code this was based on.

You Da MAN

Reply | Quote

Did you turn screenupdating off?

Reply | Quote

I resolved the issue by placing a reference to the workbook in the line of code.

Example:
Set oWB = ActiveWorkbook.Name

oWB.Sheets(“Main”).Select

Thanks to everyone who responed.
John

Reply | Quote