Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 236
  • Last Modified:

EXCEL: Array and combobox questions

Here is my code that works (mostly)

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim arPayPeriods() As String
Dim parsedPP(23) As Variant
Dim count As Integer
Dim i As Integer
count = 0
arPayPeriods = Split("Jan-1-15,Jan-16-31,Feb-1-15,Feb-16-28,Mar-1-15,Mar-16-31,Apr-1-15,Apr-16-30,May-1-15,May-16-31,Jun-1-15,Jun-16-30,Jul-1-15,Jul-16-31,Aug-1-15,Aug-16-31,Sep-1-15,Sep-16-30,Oct-1-15,Oct-16-31,Nov-1-15,Nov-16-30,Dec-1-15,Dec-16-31", ",", -1, vbBinaryCompare)
If Application.Match(Sh.Name, arPayPeriods, 0) Then
        With Sh.OLEObjects("cboSelectPP").Object
            .Clear
            For i = LBound(arPayPeriods) To UBound(arPayPeriods)
            If Not Sh.Name = arPayPeriods(i) Then
              .AddItem arPayPeriods(i)
              parsedPP(count) = arPayPeriods(i)
              count = count + 1
              End If
         Next i
        End With
        End If
End Sub

Open in new window


Here are my questions:

This line works great if the Sh.Name is in the array
If Application.Match(Sh.Name, arPayPeriods, 0) Then
How do I get it to NOT error when Sh.Name is NOT in the array?

What event do I use to grab the selected item text out of the combobox?
0
gpchicago08
Asked:
gpchicago08
  • 5
  • 4
  • 4
  • +1
2 Solutions
 
Rory ArchibaldCommented:
If Not Iserror(Application.Match(Sh.Name, arPayPeriods, 0)) Then

Open in new window


you can use the change or click events of the combobox, though you will probably need a flag variable to prevent your current code from triggering the change event also.
0
 
Martin LissRetired ProgrammerCommented:
MsgBox ComboBox1.Text
0
 
Shanan212Commented:
dim e as integer, stoR 
stoR = 0

on error resume next
stoR = Application.Match(Sh.Name, arPayPeriods, 0)
e = err.number
on error goto 0

if e = 0 then 'this will replace the line> If Application.Match(Sh.Name, arPayPeriods, 0) Then
else 'this is new - capturing error
         msgbox sh.name & " not found!"
end if

Open in new window

0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
Rory ArchibaldCommented:
@Shanan2012,

application.match will return an error value if the lookup item is not found, not raise a runtime error, so err.number will still be 0.
0
 
Shanan212Commented:
Thanks Rorya on clearing that. Gpchicago08, please ignore my solution!
0
 
gpchicago08Author Commented:
@rorya
Thanks!  That worked perfectly.

Please forgive me as I'm not fully versed in VBA, can you give a quick example or explanation of the flag variable??
0
 
Rory ArchibaldCommented:
Sure. At the top of a normal module add something like

Public bSkipEvents as Boolean

Open in new window


Then your change event needs to start with

If bSkipEvents Then Exit Sub

Open in new window


and your code to reset/populate the list needs:
bSkipEvents = True

Open in new window

at the top of it and then
bSkipEvents = False

Open in new window


at the end so that, even though the change event is still called when you reset the list, the code simply exits immediately and returns to the code that populates the control.

Does that make sense?
0
 
gpchicago08Author Commented:
Perfect

Here's the code I'm using for the second part of my question - the _Click event.

I'm getting and Object Required error for line 4.

Private Sub cboSelectPP_Click()
MsgBox cboSelectPP.Text
  Application.ScreenUpdating = False
  Activeworksheet.Range("F16:F41").Value = Worksheets(cboSelectPP.Text).Range("F16:F41").Value
  Application.ScreenUpdating = True
End Sub

Open in new window

0
 
Martin LissRetired ProgrammerCommented:
What is the value of cboSelectPP.Text?
0
 
gpchicago08Author Commented:
Text "Jan-1-15"

I'm assuming I would need the CodeName (Sheet6) of the worksheet?
0
 
Martin LissRetired ProgrammerCommented:
Try it again. When the error happens at line 4 type Ctrl+g to view the Immediate Window and type cboSelectPP.Text = "Sheet6" and then press F5. Does it work?
0
 
gpchicago08Author Commented:
No.  Doesn't work. Still get the same error.
0
 
Martin LissRetired ProgrammerCommented:
I'm surprised. Maybe there's something else going on but are you aware that you can have your combobox contain more that one column? Here's an example where it is set up to have 2 columns but only the first column visible. If it's not too much trouble you could try this with your sheet names.

    Dim MyArray(2, 1) As String

    MyArray(0, 0) = "Sheet1"
    MyArray(0, 1) = "Test"
    MyArray(1, 0) = "Sheet2"
    MyArray(1, 1) = "Test2"
    MyArray(2, 0) = "Sheet3"
    MyArray(2, 1) = "Test3"
 
    With cboSelectPP
        .ColumnCount = 2
        .ColumnWidths = "80;0"
        .List = MyArray
    End With
    MsgBox cboSelectPP.List(0, 1)
    'or
   'cboSelectPP.List(cboSelectPP.ListIndex, 1)

Open in new window

0
 
Rory ArchibaldCommented:
Activeworksheet on line 4 should be activesheet
0
 
gpchicago08Author Commented:
Thanks again rorya!!!  Simple mistake on my part!  It works!
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 5
  • 4
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now