COMBO BOX (ACTIVEX CONTROL)

[JUALOP60]

Hi,

how can i make this code work in Combo Box (Activex Control) to populate data fields in excel?

I got it to work in Combo Box (Form Control) but it will not work in Combo box (Activex control).
The reason I want combo box (Activex control) is because you auto complete can display more columns for my data (Customer database).



Here it's the code:


Dim LINDEX As Integer

Dim LAddress As String
Dim LPhoneNbr As String

Dim LRow As Long
Dim LFound As Boolean

'Retrieve project number number
LINDEX = Range("F30").Value

'Move to PAYABLES
Sheets("PAYABLES").Select

LFound = False

LRow = 3

Do While LFound = False
'Found matching project, now update address and phone number information on Sheet2
If Range("A" & LRow).Value = LINDEX Then
LFound = True
LAddress = Range("C" & LRow).Value
LPhoneNbr = Range("C" & LRow).Value

Sheets("ChequeReq").Select
Range("C15").Value = LAddress
Range("C16").Value = LPhoneNbr

'Encountered a blank project number (assuming end of list on Sheet1)
ElseIf IsEmpty(Range("A" & LRow).Value) = True Then
MsgBox ("No match was found for combo box selection.")
Exit Sub
End If

LRow = LRow + 2
Loop

Another thing,

is it possible to display heading or 2 column in a list validation box with macro?

I have chart of account and i want people to pick the account and see the description of the account just bound to column 1?
attaching attachment too excel file.

my email is:

thank you very much

 

[kjv1611]

JUALOP60,

Welcome to tek-tips. A couple of things up front:

1. When you have multiple different issues, it's tempting to put them all in the same question. But make sure they are either dependent upon one another or really closely related before doing so. Oftentimes, you actually need 2 separate posts for 2 different issues/question.

2. Posting of email addresses on the forum boards is expressly prohibited for good reason. The site posting policies specify that, which you should have gotten in your email, or at least a link to them... which I can't seem to locate at this moment. You probably figured that out since your address doesn't show up in your post... someone else saw it and removed it, or a filter removed it..

3. Try your best to find the appropriate forum. With so many to choose form, it's easy to go to the wrong one.

Anyhow, all that in mind,
This question would fit best here:
forum707

But for now, here's a thought:

1. Put your code in a Sub Procedure in a module under your Word Doc object in the VB Editor.
2. Then CALL that procedure from the ActiveX's code.

That way, any diff of handling code between "regular" and active x controls will be nullified... or so it would seem..

 

[SkipVought]

hi,

I do not see code for a combobox in your code.

It would be part of the Shapes collection on that sheet object. Refer to it by the name property...

With YourSheetObject.Shapes("YourComboBoxName")
  MsgBox .OLEFormat.Object.Value
end with

Data Validation dropdowns can only reference 1) a delimited list or 2) a reference to a single row or column, as I am sure you have already seen, having tried a multi-column list.


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[JUALOP60]

Hi,

Thank you very for your responses.

I created the sub procedure to call the macro but

I am getting the error couldn't find the file, check the speeling etc... (But the spelling is right).
error "1004".

Here it's the code:


Private Sub Companies_Change()
Dim XL As Object

Set XL = CreateObject("Excel.Application")

XL.Workbooks.Open "C:\Users\JOSE\Desktop\ChequeRe\Cheque Requisition - Template2.xlsm"

XL.Run "PopulateData"


End Sub


Thank you again

 

[SkipVought]

What application are you coding in?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[JUALOP60]

Hi

in Microsoft excel.

To call a macro in payable sheet in same workbook


thank you

 

[SkipVought]

If you are codeing in Excel VBA, you do not need to set the application object.

You would be better off, haveing all your macro code in a MODULE, rather than a sheet object.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[JUALOP60]

Hi,

i use this code to call the "PopulateData" macro:

Private Sub Companies_Open()
'Macro purpose: To send the user to the home range
Call PopulateData
End Sub

but now i am getting this message:

run time error 1004, method range of object_worksheet failed.

The "PopulatedData" macro is my first posting in this thread.

thanks for all your help guys.