Userform Aanmaken dmv VBA

[SjofaaSj]

Met onderstaande code zet ik een voorwaardelijke opmaak op een 'TABEL'
Waneer een opgegeven waarde voorkomt, wordt de volledige rij gekleurd:

Sub Format_ConditionalFormatOnTableValue()
    Dim myTbl As Excel.ListObject
    Dim myRng As Range
    Dim myRow1 As String
    Dim myName As String
    Dim myVal As String
    Dim cnt As Integer
    Dim rng As Range

'set parameters
    myName = ""
    cnt = ActiveSheet.ListObjects.Count
    If cnt = 0 Then
        MsgBox "ACTION CANCELLED" & chr(10) & chr(10) & "No Table on Active Sheet", vbCritical, "ERROR"
        Exit Sub
    ElseIf cnt > 1 Then 'multiple tables on sheet
        On Error Resume Next
        With Selection.Cells(1)
            Set rng = Intersect(.EntireRow, ActiveCell.ListObject.DataBodyRange)
            On Error GoTo 0
            If rng Is Nothing Then 'cursor is NOT in a table
StopMultiple_:
                MsgBox "ACTION CANCELLED" & chr(10) & chr(10) & "Multiple Tables on Sheet and none is selected." & chr(10) & _
                "Please select a cell in the requested table and restart procedure.", vbCritical, "ERROR"
                Exit Sub
            Else
                myName = ActiveCell.ListObject.Name
                myMess = MsgBox("ACTION WARNING" & chr(10) & "Multiple Tables on Sheet" & chr(10) & _
                "Is cursor in the requested table:" & chr(10) & "   '" & myName & "'", vbExclamation + vbYesNo, "ERROR")
                If myMess = vbNo Then
                    GoTo StopMultiple_
                End If
            End If
        End With
    End If
    
    If myName = "" Then
        Set myTbl = ActiveSheet.ListObjects(1)
    Else
        Set myTbl = ActiveSheet.ListObjects(myName)
    End If
    With myTbl
        Set myRng = .DataBodyRange
        myRow1 = .ListRows(1).Range.Address(rowAbsolute:=False, ColumnAbsolute:=True)
    End With
    myVal = """SALES"""
'define Cond Format
    With myRng
        .FormatConditions.Delete
        .FormatConditions.Add Type:=xlExpression, Formula1:="=AANTAL.ALS(" & myRow1 & ";" & myVal & ")<>0"
        .FormatConditions(.FormatConditions.Count).SetFirstPriority
        With .FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .Color = 8420607
            .TintAndShade = 0
        End With
        .FormatConditions(1).StopIfTrue = False
    End With
End Sub

De procedure werkt perfect, maar ik ben niet tevreden over de manier waarop de zoekterm wordt ingegeven.
Niet alleen zit deze momenteel vast in de code maar ik wil ook kunnen kiezen

• alleen de cellen die exact de zoekwaarde bevatten
• alleen de cellen die beginnen met de zoekwaarde
• alleen de cellen die eindigen met de zoekwaarde
• alle cellen die de zoekwaarde bevatten

Dat valt weliswaar te verhelpen dmv een inputbox en door te werken met * voor en/of achter de zoekterm,
maar dat vind ik weinig gebruiksvriendelijk en komt niet echt professioneel over.

Daarom wilde ik graag programmatorisch een userform aanmaken met

• 1 Textbox voor de zoekwaarde
• 4 OptionButtons voor de hierboven beschreven opties
• 2 CommandButtons: 'uitvoeren' en 'annuleren'

waarbij de userform zichzelf nadien vernietigd en het resultaat wordt doorgegeven naar mijn macro.

Op verschillende sites wordt het automatisch aanmaken van een userform beschreven

http://j-walk.com/ss/excel/tips/tip76.htm
http://www.excelforum.com/excel-programming-vba-macros/466519-create-userform-programmatically.html
http://www.tek-tips.com/faqs.cfm?fid=5757​

maar ik slaag er niet in om eruit te distilleren wat ik nodig heb.

Zou iemand me hierbij kunnen assisteren aub?

 

[alphamax]

Met een anoniem voorbeeldbestandje wordt je sneller en beter geholpen.

 

[WHER]

Zonder voorbeeldbestand, op basis van je links (en mijn hobbymatige kennis van VBA, dus geen garanties):

 

[SjofaaSj]

ALPHAMAX, je hebt gelijk, dus bij deze een ingesloten file.

WHER, je bent te bescheiden ; jouw code werkt uitstekend
(ik heb enkel de layout beetje aangepast, maar dat is een persoonlijke interpretatie).



Maar ik kan het resultaat niet verwerken in mijn code, of beter gezegd, ik krijg het resultaat wel door,
maar er moet een syntaxfout zitten bij het overnemen van die waarde in de formule van de voorwaardelijke opmaak.

Ik heb al zitten stoeien (lees: knoeien) met accenten en aanhalingstekens her en der & voor en achter,
want volgens mij is het niet meer dan dat, maar het juiste komt er maar niet uit.

???
Bekijk bijlage UserformAanmakenVBA.xlsb

 

[jkpieterse]

Waarom mag de userform niet blijven bestaan?

 

[SjofaaSj]

Waarom mag de userform niet blijven bestaan?

Omdat ik dit een bijzonder interessant gegeven vind en ik dit principe ook wil gebruiken in andere files
zonder dat er telkens een aparte userform moet aangemaakt worden.

Ik zie dit meer als een uitgebreide MessageBox ; waar die standaard beperkt is tot Yes/No/Cancel,
heb ik hiermee de mogelijkheid om de user meerdere keuzes aan te bieden en daarmee aan de slag te gaan.

 

[WHER]

Probeer het eens met bestand in bijlage.

 

[jkpieterse]

Probleem met at runtime een userform maken is dat je evengoed een klassemodule nodig zal hebben voor de control events. En of je nou een userform in je project hebt staan of een klasse module maakt weining verschil.

 

[jkpieterse]

Bovendien heb je bij het voorbeeld van WHER twee problemen:

1. Het design en de code voor de userform staan nu hard-coded in een VBA module
2. Je moet allow access to VBA project aan hebben staan anders werkt het niet.

 

[SjofaaSj]

Jan Karel
Ik moet eerlijkheidshalve erkennen dat m'n VBA kennis (nog) ontoereikend is om te vatten wat je precies bedoelt.

Alle respect voor je inbreng en kennis. en je zal me nooit horen pretenderen dat mijn aanpak de beste is,
maar ik probeer te behelpen en de users een sneller en duidelijker alternatief te bieden dan wat ik tot nu toe deed.

Want als ik geen userform wilde aanmaken, werkte ik tot nu vaak met opeenvolgende messageboxen, bvb:

Sheets sorteren volgens alfabet of celwaarde?

YES = alfabetisch
NO = celwaarde
CANCEl = exit​

Wil je Oplopend of Aflopend sorteren?

YES = oplopend
NO = aflopend
CANCEL = exit​

Dat vond ik weinig userfriendly en daarom zag ik dit als makkelijk aanpasbaar alternatief voor het schrijven van code
met ofwel opeenvolgende messagebpxen ofwel het apart ontwerpen van een userform.

Dit bundelt alles in code (met alle beperkingen aan layout en functionaliteit vandien)
en biedt me de kans om op een snelle manier de user een aantal directe keuzes voor te schotelen.


WHER

Neen, de code werkt niet...
Tot mijn oog viel op de COUNTIF ; ik veranderde dat (in mijn NL-talige excel) naar AANTAL.ALS en dan ging het foutloos.
Vreemd dat hij viavia die aanhalingstekens wel wil nemen en ik het er niet rechtstreeks kan ingeven, maar het resultaat telt.
HARTELIJK BEDANKT voor je hulp!!
:thumb:

 

[jkpieterse]

Mijn punt is, dat je een userform normaal gesproken aan je VBAproject toevoegt "at design time", dus in de VBA editor en niet "at run time" dus met behulp van VBA code. Je maakt de userform dan gewoon klaar zoals je hem gebruiken wilt, maar maakt die delen dynamisch die je nodig hebt. Zoals teksten op knoppen, tekst in de titel, de vraag die je wilt stellen, enzovoorts.

Door het te doen zoals in het voorbeeld van WHER moet je de code die bij je userform hoort bewerken in tekst strings in ander VBA code, hetgeen uitermate lastig is omdat je geen intellisense krijgt en geen syntax controle. Op die manier is het beter om gewoon de userform in je project te plaatsen en de code in het codevenster dat erbij hoort.

 

[snb]

Ik zie inderdaad eveneens geen enkel voordeel in de aanmaak van een userform door VBA.
Wat is simpeler dan 26 regels code ? (zeker vergeleken met de moeizame in het voorbeeld)

 

[SjofaaSj]

Ok heren.
Voor alle duidelijkheid, ik spreek jullie niet tegen.

SNB, jouw code is aanzienlijk beknopter en alles is in feite gebundeld in één enkele SUB
(bedankt ook dat je de moeite nam om die te schrijven en te reageren).
Maar ik vind het minstens even belangrijk dat ik heel goed snap wat er gebeurt,
en omdat mijn VBA kennis nog lang niet toereikend is, wringt daar het schoentje.

Maar ik heb nu een mooi voorbeeld hoe het beter kan
en ga m'n tijd nemen om dit stapsgewijs proberen ontcijferen.

Want wat ik intussen al geleerd heb in, zowel Excel als VBA,

Hoe meer je er van weet
Hoe beter je eigenlijk weet
dat je nog maar weinig weet.
En ik wil nog veel weiniger weten!​