Hiding Row/Column headers in Excel
Hiding Row/Column headers in Excel
Hiding Row/Column headers in Excel
Hiding Row/Column headers in Excel
Hiding Row/Column headers in Excel
Hiding Row/Column headers in Excel Hiding Row/Column headers in Excel Hiding Row/Column headers in Excel Hiding Row/Column headers in Excel Hiding Row/Column headers in Excel Hiding Row/Column headers in Excel Hiding Row/Column headers in Excel Hiding Row/Column headers in Excel
Hiding Row/Column headers in Excel Hiding Row/Column headers in Excel
Hiding Row/Column headers in Excel
Go Back  Xtreme Visual Basic Talk > > > Hiding Row/Column headers in Excel


Reply
 
Thread Tools Display Modes
  #1  
Old 05-20-2006, 03:03 PM
Relentless Relentless is offline
Regular
 
Join Date: May 2003
Posts: 84
Default Hiding Row/Column headers in Excel


Is there a way to hide the row and column headers and the sheet tabs in Excel ? (without using Aspose.Cells) Basically I want to access the Tools | Options menu from vb.net 2003.

Also can I make a custom menu and put it over the standard Excel menu so the user can only access my menu?

Any help is greatly appreciated.

Thanks
Rel
Reply With Quote
  #2  
Old 05-20-2006, 08:19 PM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

You can do both...

For the first one, I suggest that you use the Macro Recorder under Tools > Macros > Record Macro... and record that which you want to do and then look at the VBA code that results. Then convert the code to VB.NET.

Controlling the Menu's is also doable, but more complicated. In general, each CommandBar needs to be set to .Visible = False. (In the case of the main "Worksheet Menu Bar", you can only use .Enabled = False.) You can also use the Macro Recorder to get a sense of the coding here as well.

However, it's usually not very nice to the user to hide all their custom toolbars. Some users are not so adept at finding them once hidden. You are better off providing your Toolbar without altering the other toolbars that are present.
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb
Reply With Quote
  #3  
Old 05-21-2006, 08:08 AM
Relentless Relentless is offline
Regular
 
Join Date: May 2003
Posts: 84
Default

Thanks for the info, Mike. I plan on putting everything back to "normal" when I close my app.

Another question... Is there a faster way to iterate through the cells in my worksheet? This seems kinda slow.
Code:
Private Sub ReadCells(ByVal Sht As Excel.Worksheet) Dim intRows, intCols As Integer Dim r, c As Integer, CellText As String intRows = 45 'Sht.UsedRange.Rows.Count intCols = 65 'Sht.UsedRange.Columns.Count For r = 1 To intRows For c = 1 To intCols CellText = CType(Sht.Cells(r, c), Excel.Range).Text If CellText = "1528" Then '<----For example MessageBox.Show(CellText) End If Next Next End Sub

I saw you reply down the page and tried this:
Code:
Dim myArray(,) As Object myArray = Sht.UsedRange.Value For i = myArray.GetLowerBound(0) To myArray.GetUpperBound(0) For j = myArray.GetLowerBound(1) To myArray.GetUpperBound(1) If myArray.GetValue(i, j) = "1922" Then MessageBox.Show(i & " " & j) End If Next j Next i
This is much quicker... Thank you!

Last edited by Relentless; 05-21-2006 at 10:13 AM.
Reply With Quote
  #4  
Old 05-21-2006, 11:07 AM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

Yes, iterating through cells one-by-one is very slow, even when using VB 6.0. When using .NET it is even slower because each cell creates a new range object, which is then wrapped within a runtime callable wrapper ("RCW") for each instance. Your code above is creating 65x45 = 2925 COM objects and wrapping them each in a RCW, so this is also 2925 managed .NET objects, each pointing to their COM equivalent...

Ok, so what to do? The solution is to pop all your data into an Array and then iterate that. Unfortunately, you are using the Range.Text property and there is no way to simply return an Array of Text values from a Range of cells into an Array in one shot. If you were willing to go with the Range.Value, however, then you could use use something like this:
Code:
Private Sub ReadCells(ByVal Sht As Excel.Worksheet) Dim rng As Excel.Range With Sht rng = .Range(.Cells(1, 1), .Cells(intRows, intCols)) End With Dim objArray As Object(,) objArray = CType(rng.Value, Object(,)) ' <-- This is the key! Dim intRows As Integer = 45 Dim intCols As Integer = 65 For r As Integer = 1 To intRows For c As Integer = 1 To intCols Dim cellText As String = CType(objArray(r, c), String) If cellText = "1528" Then MessageBox.Show (cellText) End If Next c Next r End Sub
For a discussion of pushing and retrieving an Array of values from an Excel range, you can see the following thread on our sister-forum Extreme Dot Net Talk:

Why is this running so slow?

I hope this helps!
,
Mike
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb
Reply With Quote
  #5  
Old 05-21-2006, 11:12 AM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

Heh, I guess I was too slow... I'm glad you found it.

Anyway, I hope the code I showed above helps out anyway.
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb
Reply With Quote
  #6  
Old 05-22-2006, 09:41 AM
Relentless Relentless is offline
Regular
 
Join Date: May 2003
Posts: 84
Default

Good stuff, Mike. Thanks again. One more question... for now. Can I hide the file name in the title bar? I'm specifying a caption... which I like, but I dont want the filename to show.

Thanks
Rel
Reply With Quote
  #7  
Old 05-22-2006, 09:58 AM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

Well, there is Application.Caption, but that only changes the display name of the Application, without suppressing the name of the open document. I guess you'd have to use an API then... SetWindowText comes to mind.

-- Mike
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
Hiding Row/Column headers in Excel
Hiding Row/Column headers in Excel
Hiding Row/Column headers in Excel Hiding Row/Column headers in Excel
Hiding Row/Column headers in Excel
Hiding Row/Column headers in Excel
Hiding Row/Column headers in Excel Hiding Row/Column headers in Excel Hiding Row/Column headers in Excel Hiding Row/Column headers in Excel Hiding Row/Column headers in Excel Hiding Row/Column headers in Excel Hiding Row/Column headers in Excel
Hiding Row/Column headers in Excel
Hiding Row/Column headers in Excel
 
Hiding Row/Column headers in Excel
Hiding Row/Column headers in Excel
 
-->