• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Is it possible to use Indirect AND combo box?

which excel using? 2003 / 2007 / 2013
if you have sample file plz upload to get quick result.
 
AVK said:
which excel using? 2003 / 2007 / 2013
if you have sample file plz upload to get quick result.
Click to expand...

The combo box works for the first column. The second column uses =Indirect and the combo box doesn't work for that column. Can =Indirect and the combo box work, concurrently?

In addition, I don't like how the combo box "image" seems to "hang" there after you've moved away from the cell, or at least it leaves some weirdness on the cell afterwards. Any idea what that is?
 

Attachments

  • sample.xlsm
    53.2 KB · Views: 13
Change below portion of your code.
Code: 
        str = Target.Validation.Formula1
        str = Right(str, Len(str) - 1)

To something like...
Code: 
    If Target.Column = 2 Then
        x = Split(Target.Validation.Formula1, "(")
        str = Left(x(UBound(x)), Len(x(UBound(x))) - 1)
        str = Range(str).Value
    Else
        str = Target.Validation.Formula1
        str = Right(str, Len(str) - 1)
    End If

Also add "Dim x" at variable declaration part of your code.

This is needed as your Column 1 validation formula and Column 2 validation formula has different syntax and string structure (i.e. "=PrefixProject" vs. "=INDIRECT($A4)")
 
Thank you so much for your responses.

I don't actually know what I'm doing with VBA, so what do you mean by adding "Dim x" at the "variable declaration part of your code"? What part of the code is that?
 
Top of your code looks like this. Just add another line below. "Dim x"

Code: 
Private Sub Worksheet_BeforeDoubleClick _
  (ByVal Target As Range, _
    Cancel As Boolean)
Dim str As String
Dim cboTemp As OLEObject
Dim ws As Worksheet

It's declaring variable called "x" as variant. It's used to store array of split string for manipulation in this case.
 
Thank you, once again! :)

I'm so thankful that you know what you're doing (because I sure don't)! I'll give this a try....
 
I need to run this on many sheets in one workbook. So, when I copy the code from one sheet to the next, obviously, I get an error. How do I amend the code to make it work for all of them? I really appreciate all this help.
 
Upload sample workbook if you can.

I suspect you'd need to place the code in ThisWorkBook module. In Workbook_SheetChange event.
 
Here's a sample. It would need to run on each Month sheet.
Best regards,
Peacedout
 

Attachments

  • 2017 Sample.xlsm
    64.2 KB · Views: 10
Here you go. If you copy one of the sheet (January to March) it will replicate code as well.

Right click on sheet tab. Select "Move or Copy..." and make sure you have "Create a copy" checked. Once copied, rename the sheet.

By doing this, you don't need to recreate ComboBox, Data Validation etc.

The reason for the error, is that when non-worksheet object is activated when you set "ws" variable using "ActiveSheet" it gives type mismatch.

To avoid this, you can eliminate "ws" variable. Since there is only one location that variable is used in the code. See modified section of your code below.

Code: 
Private Sub Worksheet_BeforeDoubleClick _
  (ByVal Target As Range, _
    Cancel As Boolean)
Dim str As String
Dim cboTemp As OLEObject
Dim x

Set cboTemp = Target.Parent.OLEObjects("TempCombo")

Target is the cell and ".Parent" specifies the sheet it belongs to.
 

Attachments

  • 2017 Sample.xlsm
    72.7 KB · Views: 24
I'm almost finished with this :) I have twelve sheets. It would have been more work to recreate the REST of the info on the sheets than to recreate the combo box and the data validation on each sheet, so that's what I did. My problem now is that on ONE of the sheets, the drop down list does NOT appear adjacent to the cell you've double-clicked, but way over on the left side of the sheet (like, nowhere near the cell). I THOUGHT I had read some info about this SOMEWHERE on the web, but now can't find it. Does this ring any bells with anyone? On the other 11 sheets, the combo boxes work just fine and the drop down lists appear where they should.
 
I have no idea what's going on, but now when I open the file, everything appears to be working just fine. However, if you've experienced what I describe above (about the drop down list appearing on the left of the page) don't hesitate to comment!
 
You must log in or register to reply here.
Back
Top