How can I declare the variable from the list for ADODB query ?

%3CLINGO-SUB%20id%3D%22lingo-sub-2061347%22%20slang%3D%22en-US%22%3EHow%20can%20I%20declare%20the%20variable%20from%20the%20list%20for%20ADODB%20query%20%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2061347%22%20slang%3D%22en-US%22%3EHello%20Experts%2C%3CBR%20%2F%3EI%20have%20a%20liste%20of%20choices%20with%20100%20variables%2C%20I%20would%20like%20to%20do%20this%3A%3CBR%20%2F%3EPrivate%20Sub%20Worksheet_Change(ByVal%20Target%20As%20Range)%3CBR%20%2F%3EIf%20Target.Address(0%2C0)%3D%C2%AB%26nbsp%3BE15%26nbsp%3B%C2%BB%20then%3CBR%20%2F%3EN_C_bulletin%20Target.Value%3CBR%20%2F%3EEnd%20if%3CBR%20%2F%3EEnd%20Sub%3CBR%20%2F%3E%3CBR%20%2F%3EN_C_bulletin%20is%20Sub%20ADODB%20where%20I%20use%20(strQ%20As%20String)%3CBR%20%2F%3E%3CBR%20%2F%3ESo%20I%20just%20want%20to%20chose%20the%20value%20from%20the%20list%20by%20clicking%20on%20it%20and%20run%20the%20sql%20query%20after.%3CBR%20%2F%3EBut%20my%20first%20function%20doesn%E2%80%99t%20work.%20Maybe%20because%20my%20list%20is%20in%20E%20and%20F%20(%20I%20merged%20to%20columns).%3CBR%20%2F%3E%3CBR%20%2F%3EThank%20you%20for%20your%20help%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2061347%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2061602%22%20slang%3D%22en-US%22%3ERe%3A%20How%20can%20I%20declare%20the%20variable%20from%20the%20list%20for%20ADODB%20query%20%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2061602%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F884489%22%20target%3D%22_blank%22%3E%40MarieParis%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EVBA%20uses%20straight%20quotes%20%22...%22%2C%20not%20French%20quotes.%20So%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20Target.Address(False%2C%20False)%20%3D%20%22E15%22%20Then%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2061782%22%20slang%3D%22en-US%22%3ERe%3A%20How%20can%20I%20declare%20the%20variable%20from%20the%20list%20for%20ADODB%20query%20%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2061782%22%20slang%3D%22en-US%22%3EHello%2C%3CBR%20%2F%3EThank%20you%20!%3CBR%20%2F%3EI%20replaced%2C%20but%20it%20doesn%E2%80%99t%20work%20(%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2061812%22%20slang%3D%22en-US%22%3ERe%3A%20How%20can%20I%20declare%20the%20variable%20from%20the%20list%20for%20ADODB%20query%20%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2061812%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F884489%22%20target%3D%22_blank%22%3E%40MarieParis%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EClick%20in%20the%20line%3C%2FP%3E%0A%3CP%3EPrivate%20Sub%20Worksheet_Change(ByVal%20Target%20As%20Range)%3C%2FP%3E%0A%3CP%3Eand%20press%20F9%20to%20set%20a%20breakpoint.%3C%2FP%3E%0A%3CP%3EChange%20cell%20E15%20and%20press%20Enter.%3C%2FP%3E%0A%3CP%3EThe%20code%20will%20pause%20at%20the%20above%20line.%3C%2FP%3E%0A%3CP%3EYou%20can%20press%20F8%20repeatedly%20to%20execute%20the%20code%20line%20by%20line.%3C%2FP%3E%0A%3CP%3EDoes%20that%20provide%20a%20clue%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2061861%22%20slang%3D%22en-US%22%3ERe%3A%20How%20can%20I%20declare%20the%20variable%20from%20the%20list%20for%20ADODB%20query%20%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2061861%22%20slang%3D%22en-US%22%3EF8%20doesn%E2%80%99t%20work%20at%20all%20(%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2061863%22%20slang%3D%22en-US%22%3ERe%3A%20How%20can%20I%20declare%20the%20variable%20from%20the%20list%20for%20ADODB%20query%20%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2061863%22%20slang%3D%22en-US%22%3EWhat%20does%20it%20mean%20The%20code%20will%20pause%20at%20the%20above%20line%20%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2061880%22%20slang%3D%22en-US%22%3ERe%3A%20How%20can%20I%20declare%20the%20variable%20from%20the%20list%20for%20ADODB%20query%20%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2061880%22%20slang%3D%22en-US%22%3EI%20would%20like%20just%20click%20on%20the%20variable%20in%20the%20list%20and%20after%20use%20this%20variable%20on%20my%20sql%20query.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2061935%22%20slang%3D%22en-US%22%3ERe%3A%20How%20can%20I%20declare%20the%20variable%20from%20the%20list%20for%20ADODB%20query%20%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2061935%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F884489%22%20target%3D%22_blank%22%3E%40MarieParis%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ECould%20you%20attach%20a%20copy%20of%20the%20workbook%20(without%20sensitive%20information)%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2063879%22%20slang%3D%22en-US%22%3ERe%3A%20How%20can%20I%20declare%20the%20variable%20from%20the%20list%20for%20ADODB%20query%20%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2063879%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3CBR%20%2F%3EI%20Understood%2C%20I%20have%20mostly%20the%20connection%20error.%3CBR%20%2F%3EI%20don%E2%80%99t%20know%20if%20I%20placed%20the%20connection%2Fdeconnection%20at%20right%20place.%3CBR%20%2F%3EMy%20code%20is%3CBR%20%2F%3E%3CBR%20%2F%3EPublic%20Sub%20GET_GROUPE_GESTION_CIBLE(ByRef%20strQ%20As%20String)%3CBR%20%2F%3EDim%20cnn_Pegase%20As%20ADODB.Connection%20'Connexion%20Pegase%3CBR%20%2F%3ECall%20CONNEXION_PEGASE(%22selection%22%2C%20%22verify%22%2C%20%22PEG_DUP%22)%3CBR%20%2F%3E%3CBR%20%2F%3EDim%20RECSET%20As%20New%20ADODB.Recordset%3CBR%20%2F%3E%3CBR%20%2F%3ERECSET.Open%20%22select%20serv.lb_long%7C%7C'%20(AT%3A%20'%7C%7Cpers.s_nom%7C%7C'%20'%7C%7Cpers.s_prenom%7C%7C')'%20as%20groupecible%20from%20db_tiers%20as%20ti%2C%20dr_collaborateur_tiers%20cp%2Cdb_collaborateur%20col%2Cdb_personne%20pers%2Cdr_service_collaborateur%20sc%2Cdb_service_compagnie%20serv%22%20%26amp%3B%20_%3CBR%20%2F%3E%22%20where%20ti.CD_TIERS%3D'%22%20%26amp%3B%20strQ%20%26amp%3B%20%22'%20ti.is_tiers%3Dcp.is_tiers%20and%20cp.is_collaborateur%20%3D%20col.is_collaborateur%20and%20col.is_personne%20%3D%20pers.is_personne%20and%20col.is_collaborateur%20%3D%20sc.is_collaborateur%20and%20sc.is_service_compagnie%20%3D%20serv.is_service_compagnie%22%2C%20cnn_Pegase%2C%20adOpenDynamic%2C%20adLockBatchOptimistic%3CBR%20%2F%3EIf%20Not%20RECSET.EOF%20Then%3CBR%20%2F%3EWorksheets(%221%20-%20Feuille%20de%20Suivi%20Commercial%22).Range(%22GET_GROUPE_GESTION_CIBLE%22).Value%20%3D%20RECSET.Fields(%22groupecible%22).Value%3CBR%20%2F%3EElse%3CBR%20%2F%3EWorksheets(%221%20-%20Feuille%20de%20Suivi%20Commercial%22).Range(%22GET_GROUPE_GESTION_CIBLE%22).Value%20%3D%20%22Inconnu%22%3CBR%20%2F%3EEnd%20If%3CBR%20%2F%3ERECSET.Close%3CBR%20%2F%3ECall%20DECONNEXION_PEGASE%3CBR%20%2F%3EEnd%20Sub%3CBR%20%2F%3E%3CBR%20%2F%3EThe%20code%20of%20connection%20error%20is%203709%3CBR%20%2F%3EThank%20you%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2064406%22%20slang%3D%22en-US%22%3ERe%3A%20How%20can%20I%20declare%20the%20variable%20from%20the%20list%20for%20ADODB%20query%20%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2064406%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F884489%22%20target%3D%22_blank%22%3E%40MarieParis%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWe're%20not%20getting%20any%20further.%20Perhaps%20someone%20else%20can%20help%20you.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor
Test
9 Replies

@MarieParis 

VBA uses straight quotes "...", not French quotes. So:

 

If Target.Address(False, False) = "E15" Then

Hello,
Thank you !
I replaced, but it doesn’t work (

@MarieParis 

Click in the line

Private Sub Worksheet_Change(ByVal Target As Range)

and press F9 to set a breakpoint.

Change cell E15 and press Enter.

The code will pause at the above line.

You can press F8 repeatedly to execute the code line by line.

Does that provide a clue?

F8 doesn’t work at all (
What does it mean The code will pause at the above line ?
I would like just click on the variable in the list and after use this variable on my sql query.

@MarieParis 

Could you attach a copy of the workbook (without sensitive information)?

My code is

@MarieParis 

We're not getting any further. Perhaps someone else can help you.