none
VB Coding Question RRS feed

  • Question

  • Awhile back I had posed a question to the forum on some VB coding I was needing assistance on. It helped quite a bit but I had posed another question to the forum at the bottom of the thread that never got responded to so I wanted to throw it out there as a new link again to see if I could get any help. My question needing to be answered is at the bottom of the link below:

     http://social.msdn.microsoft.com/Forums/en-US/vsto/thread/e56fcc2c-6518-4232-85d6-4e24303e1c0c

    • Edited by Goodrie Friday, December 2, 2011 5:49 PM
    Friday, December 2, 2011 5:48 PM

Answers

  • Hi Goodrie,

     

    Thanks for posting in the MSDN Forum.

     

    According to your description, I create following snippet for you. You are able to replace the strExceptWorksheet as the sheet name which you what to keep. This snippet is a form which I will call it via a button click event on the ribbon.

     

    Public Class Form1
        Private Application As Excel.Application
        Private Workbook As Excel.Workbook
    
        Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
            Application = Globals.ThisAddIn.Application
            Workbook = Application.ActiveWorkbook
    
            ComboBox1.Items.Clear()
            For Each objWorksheet As Excel.Worksheet In Workbook.Sheets
                ComboBox1.Items.Add(objWorksheet.Name)
            Next
            ComboBox1.SelectedIndex = 0
        End Sub
    
        Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
            Dim strExceptWorksheet As String = ComboBox1.Text
            Dim isVeryHidden As Boolean = RadioButton1.Checked
    
            Try
                Workbook.Sheets(strExceptWorksheet).Visible = Excel.XlSheetVisibility.xlSheetVisible
                For Each objWorksheet As Excel.Worksheet In Workbook.Sheets
                    If objWorksheet.Name <> strExceptWorksheet Then
                        If isVeryHidden Then
                            objWorksheet.Visible = Excel.XlSheetVisibility.xlSheetVeryHidden
                        Else
                            objWorksheet.Visible = Excel.XlSheetVisibility.xlSheetHidden
                        End If
                    End If
                Next
            Catch ex As Exception
                MsgBox(ex.Message)
            End Try
    
            Close()
        End Sub
    End Class
    

     

    I hope it can solve your issue. If you have any questions, please feel free to let me know.

     

    Have a good day,

     

    Tom


    Tom Xu [MSFT]
    MSDN Community Support | Feedback to us
    Monday, December 5, 2011 8:47 AM
    Moderator

All replies

  • Hi Goodrie,

     

    Thanks for posting in the MSDN Forum.

     

    According to your description, I create following snippet for you. You are able to replace the strExceptWorksheet as the sheet name which you what to keep. This snippet is a form which I will call it via a button click event on the ribbon.

     

    Public Class Form1
        Private Application As Excel.Application
        Private Workbook As Excel.Workbook
    
        Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
            Application = Globals.ThisAddIn.Application
            Workbook = Application.ActiveWorkbook
    
            ComboBox1.Items.Clear()
            For Each objWorksheet As Excel.Worksheet In Workbook.Sheets
                ComboBox1.Items.Add(objWorksheet.Name)
            Next
            ComboBox1.SelectedIndex = 0
        End Sub
    
        Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
            Dim strExceptWorksheet As String = ComboBox1.Text
            Dim isVeryHidden As Boolean = RadioButton1.Checked
    
            Try
                Workbook.Sheets(strExceptWorksheet).Visible = Excel.XlSheetVisibility.xlSheetVisible
                For Each objWorksheet As Excel.Worksheet In Workbook.Sheets
                    If objWorksheet.Name <> strExceptWorksheet Then
                        If isVeryHidden Then
                            objWorksheet.Visible = Excel.XlSheetVisibility.xlSheetVeryHidden
                        Else
                            objWorksheet.Visible = Excel.XlSheetVisibility.xlSheetHidden
                        End If
                    End If
                Next
            Catch ex As Exception
                MsgBox(ex.Message)
            End Try
    
            Close()
        End Sub
    End Class
    

     

    I hope it can solve your issue. If you have any questions, please feel free to let me know.

     

    Have a good day,

     

    Tom


    Tom Xu [MSFT]
    MSDN Community Support | Feedback to us
    Monday, December 5, 2011 8:47 AM
    Moderator
  • Running into an error on the line you gave me Combobox1.Items.Add(objWorksheet.Name)  

    What would be causing this?  Error below

     

    System.InvalidCastException was unhandled by user code
      Message=Unable to cast object of type 'System.String' to type 'Microsoft.Office.Tools.Ribbon.RibbonDropDownItem'.
      Source=ExcelWorkbook6
      StackTrace:
           at ExcelWorkbook6.Ribbon1.Form1_Load(Object sender, RibbonUIEventArgs e) in D:\Documents and Settings\goodrie\Desktop\PricePage\ExcelWorkbook6\ExcelWorkbook6\Ribbon1.vb:line 16
           at Microsoft.Office.Tools.Ribbon.OfficeRibbonImpl.RaiseLoadEvent(RibbonManagerImpl manager)
           at Microsoft.Office.Tools.Ribbon.RibbonManagerImpl.CreateRibbon(String ribbonId, Object context)
           at Microsoft.Office.Tools.Ribbon.RibbonManagerImpl.GetRibbon(IRibbonControl control)
           at Microsoft.Office.Tools.Ribbon.RibbonManagerImpl.FindRibbonComponent(IRibbonControl control)
           at Microsoft.Office.Tools.Ribbon.RibbonManagerImpl.Invoke(RibbonComponentCallback callback, Object[] args)
           at Microsoft.Office.Tools.Ribbon.RibbonMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
           at Microsoft.Office.Tools.Ribbon.RibbonManagerImpl.System.Reflection.IReflect.InvokeMember(String name, BindingFlags invokeAttr, Binder binder, Object target, Object[] args, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParameters)
      InnerException:

    Thursday, December 15, 2011 6:49 PM
  • Hi Goodrie,

    This is a Windows Form application, not a ribbon class.

    Have a good day,

    Tom


    Tom Xu [MSFT]
    MSDN Community Support | Feedback to us
    Monday, December 26, 2011 12:26 PM
    Moderator