Solved

EXCEL: Array and combobox questions

Posted on 2013-01-07
15
227 Views
Last Modified: 2013-01-07
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
Comment
Question by:gpchicago08
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 4
  • +1
15 Comments
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 500 total points
ID: 38751422
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
 
LVL 47

Expert Comment

by:Martin Liss
ID: 38751441
MsgBox ComboBox1.Text
0
 
LVL 13

Expert Comment

by:Shanan212
ID: 38751458
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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 
LVL 85

Expert Comment

by:Rory Archibald
ID: 38751481
@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
 
LVL 13

Expert Comment

by:Shanan212
ID: 38751501
Thanks Rorya on clearing that. Gpchicago08, please ignore my solution!
0
 

Author Comment

by:gpchicago08
ID: 38751531
@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
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 38751586
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
 

Author Comment

by:gpchicago08
ID: 38751614
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
 
LVL 47

Expert Comment

by:Martin Liss
ID: 38751630
What is the value of cboSelectPP.Text?
0
 

Author Comment

by:gpchicago08
ID: 38751636
Text "Jan-1-15"

I'm assuming I would need the CodeName (Sheet6) of the worksheet?
0
 
LVL 47

Expert Comment

by:Martin Liss
ID: 38751653
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
 

Author Comment

by:gpchicago08
ID: 38751738
No.  Doesn't work. Still get the same error.
0
 
LVL 47

Expert Comment

by:Martin Liss
ID: 38751776
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
 
LVL 85

Assisted Solution

by:Rory Archibald
Rory Archibald earned 500 total points
ID: 38751801
Activeworksheet on line 4 should be activesheet
0
 

Author Comment

by:gpchicago08
ID: 38751823
Thanks again rorya!!!  Simple mistake on my part!  It works!
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question