How to change the contents of a control in ribbon with vba? (2 Viewers)

K

KitaYama

Well-known member
Local time
Today, 12:59
Joined
Jan 6, 2022
Messages
1,541
I have several combo boxes in a custom ribbon.
How can I set a value to one of them with VBA?

2023-02-23_15-41-33.jpg


XML: 
<group id="grp_objects" getLabel="getLabel">
    <comboBox id="cmb_tbls" getLabel="getLabel" getItemCount="getItemCount" getItemLabel="getItemLabel" onChange="OnChangeComboBox"  sizeString="MMMMMMMMMMMM">
    </comboBox> 
    <comboBox id="cmb_frms" getLabel="getLabel" getItemCount="getItemCount" getItemLabel="getItemLabel" onChange="OnChangeComboBox"  sizeString="MMMMMMMMMMMM">
    </comboBox>
    <comboBox id="cmb_rpts" getLabel="getLabel" getItemCount="getItemCount" getItemLabel="getItemLabel" onChange="OnChangeComboBox"  sizeString="MMMMMMMMMMMM">
    </comboBox>
</group>

Thanks.

Edit: I can select a name from combo box. The table/form/reports opens. I need to set the combo back to blank. In above screen shot, I want to change tblEmaergencies to blank/null.
 
Last edited:
Solution
arnelgp
ok, maybe my copy got corrupted.
while you can still open the "first" db i uploaded, you can edit it instead.

1. edit mod_getRibbon module, fncGetTextCbx() sub:
Code: 
Sub fncGetTextCbx(control As IRibbonControl, ByRef strText)
If control.Id = "cbx2" Then
    If Not IsNull(TempVars!tvarText) Then
        strText = TempVars!tvarText
    Else
        strText = ""

    End If
Else
    strText = ""

End If

End Sub

Open frmCustomers in design view:
Load / Unload Events:
Code: 
Private Sub Form_Load()
On Error Resume Next
TempVars.Remove "tvarText"
objRibbon.InvalidateControl ("cbx2")
Me!cboQuery.SetFocus
End Sub

Private Sub Form_Unload(Cancel As Integer)
On Error Resume Next
TempVars.Remove "tvarText"
objRibbon.InvalidateControl...
Sort by date Sort by votes
arnelgp

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 11:59
Joined
May 7, 2009
Messages
19,245
after opening the Table/Report or Form issue:

objRibbon.InvalidateControl ("comboboxName")

from "OnChangeComboBox" sub.
 
Upvote 0 Downvote
K

KitaYama

Well-known member
Local time
Today, 12:59
Joined
Jan 6, 2022
Messages
1,541
@arnelgp I've already tested that. Invalidate reloads the contents of the combo, but doesn't set it to null or blank.

But, I'll give it another try and see the results.

thanks.
 
Upvote 0 Downvote
K

KitaYama

Well-known member
Local time
Today, 12:59
Joined
Jan 6, 2022
Messages
1,541
@arnelgp Tested again. Same result. The name of the object remains in the combo. No error at all.
This is what I have. Do you see anything that may cause this?

SQL: 
Sub OnChangeComboBox(Control As IRibbonControl, strText As String)
  
   if strText & "" = "" Then Exit Sub
   Select Case Control.ID
        Case "cmb_frms"
            DoCmd.OpenForm strText, acDesign
        Case "cmb_rpts"
            DoCmd.OpenReport strText, acViewDesign
        Case "cmb_tbls"
            DoCmd.OpenTable strText
    End Select
    
    AppRibbon.InvalidateControl Control.ID

ErrTrap2:
    On Error GoTo 0
    Application.Echo True
End Sub

Thanks again.
 
Upvote 0 Downvote
arnelgp

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 11:59
Joined
May 7, 2009
Messages
19,245
only test, can you Invalidate it by hard-coding the name:
Code: 
Sub OnChangeComboBox(Control As IRibbonControl, strText As String)
 
   if strText & "" = "" Then Exit Sub
   Select Case Control.ID
        Case "cmb_frms"
            DoCmd.OpenForm strText, acDesign
            AppRibbon.InvalidateControl ("cmb_frms")
        Case "cmb_rpts"
            DoCmd.OpenReport strText, acViewDesign
            AppRibbon.InvalidateControl ("cmb_rpts")
        Case "cmb_tbls"
            DoCmd.OpenTable strText
             AppRibbon.InvalidateControl ("cmb_tbls")
        End Select
    


ErrTrap2:
    On Error GoTo 0
    Application.Echo True
End Sub
 
Upvote 0 Downvote
arnelgp

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 11:59
Joined
May 7, 2009
Messages
19,245
see this demo, i donwloaded this and the "list" comes from a table.
 

Attachments

  • RibbonCbxDrop.accdb
    788 KB · Views: 97
Upvote 0 Downvote
K

KitaYama

Well-known member
Local time
Today, 12:59
Joined
Jan 6, 2022
Messages
1,541
While I'm trying to track why Invalidate doesn't bring back the null value, can anyone answer to the original question?
Is there any way to assign a value to a control (textbox, combo, checkbox etc) in the ribbon?

thanks.
 
Upvote 0 Downvote
arnelgp

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 11:59
Joined
May 7, 2009
Messages
19,245
open Customer form.
select a name from the combo on the Form.
see the combo on the ribbon as it changes.
 

Attachments

  • RibbonCbxDrop.accdb
    888 KB · Views: 78
Upvote 0 Downvote
K

KitaYama

Well-known member
Local time
Today, 12:59
Joined
Jan 6, 2022
Messages
1,541
arnelgp said:
open Customer form.
select a name from the combo on the Form.
see the combo on the ribbon as it changes.
Click to expand...
Tested on :
Microsoft 365 Latest version (English) & Windows 11 (English)
Office 2019 Latest updates (Japanese) & Windows 11 (Japanese)

In both cases I receive the following error when I open the form, when I select any thing from combo and when I close the form.

33.jpg


Thanks for your effort for solving this puzzle.


Edit :
I added a break point at first of all events of the form to see which line causes the error.
The error is raised before any event.
Thanks again.


Edit 2: Seems you've used a tempVar.
I will test on my app to see if I can do the same.
 
Upvote 0 Downvote
arnelgp

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 11:59
Joined
May 7, 2009
Messages
19,245
ok, maybe my copy got corrupted.
while you can still open the "first" db i uploaded, you can edit it instead.

1. edit mod_getRibbon module, fncGetTextCbx() sub:
Code: 
Sub fncGetTextCbx(control As IRibbonControl, ByRef strText)
If control.Id = "cbx2" Then
    If Not IsNull(TempVars!tvarText) Then
        strText = TempVars!tvarText
    Else
        strText = ""

    End If
Else
    strText = ""

End If

End Sub

Open frmCustomers in design view:
Load / Unload Events:
Code: 
Private Sub Form_Load()
On Error Resume Next
TempVars.Remove "tvarText"
objRibbon.InvalidateControl ("cbx2")
Me!cboQuery.SetFocus
End Sub

Private Sub Form_Unload(Cancel As Integer)
On Error Resume Next
TempVars.Remove "tvarText"
objRibbon.InvalidateControl ("cbx2")
End Sub

the combobox inside the form, AfterUpdate Event:
Code: 
Private Sub cboQuery_AfterUpdate()
Dim filter As String
filter = "cli_name = '" & Me!cboQuery.Column(1) & "'"
DoCmd.ApplyFilter , filter
Me!cli_Name.SetFocus
'Me!cboQuery = Null
TempVars!tvarText = Me!cboQuery.Column(1) & ""
objRibbon.InvalidateControl ("cbx2")
End Sub
 
Upvote 1 Downvote
Solution
K

KitaYama

Well-known member
Local time
Today, 12:59
Joined
Jan 6, 2022
Messages
1,541
arnelgp said:
ok, maybe my copy got corrupted.
while you can still open the "first" db i uploaded, you can edit it instead.

1. edit mod_getRibbon module, fncGetTextCbx() sub:
Code: 
Sub fncGetTextCbx(control As IRibbonControl, ByRef strText)
If control.Id = "cbx2" Then
    If Not IsNull(TempVars!tvarText) Then
        strText = TempVars!tvarText
    Else
        strText = ""

    End If
Else
    strText = ""

End If

End Sub

Open frmCustomers in design view:
Load / Unload Events:
Code: 
Private Sub Form_Load()
On Error Resume Next
TempVars.Remove "tvarText"
objRibbon.InvalidateControl ("cbx2")
Me!cboQuery.SetFocus
End Sub

Private Sub Form_Unload(Cancel As Integer)
On Error Resume Next
TempVars.Remove "tvarText"
objRibbon.InvalidateControl ("cbx2")
End Sub

the combobox inside the form, AfterUpdate Event:
Code: 
Private Sub cboQuery_AfterUpdate()
Dim filter As String
filter = "cli_name = '" & Me!cboQuery.Column(1) & "'"
DoCmd.ApplyFilter , filter
Me!cli_Name.SetFocus
'Me!cboQuery = Null
TempVars!tvarText = Me!cboQuery.Column(1) & ""
objRibbon.InvalidateControl ("cbx2")
End Sub
Click to expand...
It seems our server has gone through an update. I lost my remote desktop connection to my PC from home.
I'll test it as soon as I'm back to my office.

I really appreciate your help.
 
Upvote 0 Downvote
K

KitaYama

Well-known member
Local time
Today, 12:59
Joined
Jan 6, 2022
Messages
1,541
arnelgp said:
ok, maybe my copy got corrupted.
while you can still open the "first" db i uploaded, you can edit it instead.

1. edit mod_getRibbon module, fncGetTextCbx() sub:
Code: 
Sub fncGetTextCbx(control As IRibbonControl, ByRef strText)
If control.Id = "cbx2" Then
    If Not IsNull(TempVars!tvarText) Then
        strText = TempVars!tvarText
    Else
        strText = ""

    End If
Else
    strText = ""

End If

End Sub

Open frmCustomers in design view:
Load / Unload Events:
Code: 
Private Sub Form_Load()
On Error Resume Next
TempVars.Remove "tvarText"
objRibbon.InvalidateControl ("cbx2")
Me!cboQuery.SetFocus
End Sub

Private Sub Form_Unload(Cancel As Integer)
On Error Resume Next
TempVars.Remove "tvarText"
objRibbon.InvalidateControl ("cbx2")
End Sub

the combobox inside the form, AfterUpdate Event:
Code: 
Private Sub cboQuery_AfterUpdate()
Dim filter As String
filter = "cli_name = '" & Me!cboQuery.Column(1) & "'"
DoCmd.ApplyFilter , filter
Me!cli_Name.SetFocus
'Me!cboQuery = Null
TempVars!tvarText = Me!cboQuery.Column(1) & ""
objRibbon.InvalidateControl ("cbx2")
End Sub
Click to expand...
@arnelgp I'm not sure what's happening here, but the moment I add your suggested code to the downloaded file, I receive the error I mentioned in #11.

But seeing the logic you used, I was able to update our database and get the correct result.
The key was using GetText action of the combo box. It seems that invalidating the combo, doesn't take the selected text from it. GetText action should be used too. Thanks for solving the puzzle.

Once again you saved us.
I really appreciate your help and advices.
 
Upvote 0 Downvote
You must log in or register to reply here.

Similar threads

T
How to unselect a dropdown in the Ribbon
Replies
6
Views
247
GPGeorge
GPGeorge
S
  • Question
VBA code to change the border of objects on a form ... Got / Lost focus
Replies
10
Views
636
Mike Krailo
Mike Krailo
C
How to reference controls on a Ribbon
Replies
6
Views
777
MarkK
MarkK
S
Solved is it possible to alter an hyperlink in the backstage part of the Ribbon?
Replies
4
Views
393
smig
S
N
Solved How to changes thousands of products prices in a retail business (The backed is SQL Server AZURE)
Replies
3
Views
352
nector
N

Users who are viewing this thread

Total: 2 (members: 0, guests: 2)
Top Bottom