Visitors Form - Howto hide/not show an item(user) in ListBox after clicking CHECK OUT (command)button

excelstudentnl

New Member
Joined
Sep 12, 2022
Messages
6
Office Version
  1. 365
  2. 2021
  3. 2019
Platform
  1. Windows
Hello everybody,

I'm making an Visitors Registration Form. it's almost finished but i'm a newbie and i can't figure out howto ONLY SHOW users in my listbox that have not CHECKED OUT yet?
or better said, howto hide or make users not appear in the ListBox after clicking on the commandbutton "CHECK OUT" so only visitors who are still present are shown

and if this indeed is possible, should this be edited in the Listbox or the Commandbutton in VBA or my ListBox_Display_Sheet in Excel ?

Thankyou in advance
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

cmowla

Well-known Member
Joined
Sep 21, 2021
Messages
536
Office Version
  1. 365
Platform
  1. Windows
Well it's Excel. The way I handle such things is to simply make a simple table (or write Excel VBA code to make one) in sheet whose visibility is xlVeryHidden. In the table, simply have a unique identifier/primary key (such as visitor #) for the rows and one column representing "has viewed". And when the user has viewed, have Excel VBA write anything (even one character) into the cell which implies they have viewed. Then have the registration form check to see if there is anything in that cell for that visitor at startup.
 

excelstudentnl

New Member
Joined
Sep 12, 2022
Messages
6
Office Version
  1. 365
  2. 2021
  3. 2019
Platform
  1. Windows
Well it's Excel. The way I handle such things is to simply make a simple table (or write Excel VBA code to make one) in sheet whose visibility is xlVeryHidden. In the table, simply have a unique identifier/primary key (such as visitor #) for the rows and one column representing "has viewed". And when the user has viewed, have Excel VBA write anything (even one character) into the cell which implies they have viewed. Then have the registration form check to see if there is anything in that cell for that visitor at startup.
I already have a sheet with identifier # visitor1 ,2 and so on incl. columns Checked In and Checked Out, Time of Arrival, etc. When a user wants to check out he or she can select their name from the listbox on the userform and click on Check Out! But see everything including the listbox is on the userform(visitors form) and is not ment to be closed, i have a couple of textboxes/comboboxes and a checkmark box for users to fill in, and two commandbuttons (Check-In) + (Check-Out). When (checked-in) a user appears in the listbox below. But the listbox is on the bottom of the userform (visitors registration form) not on a excel sheet. When clicking on Check In or Check-Out All the data from the listbox is written to an excelsheet but also appears on the listbox on the userform. which is the reason i want to hide or remove the users who checked out already in That listbox, without deleting them from the excelsheet which are ment for admins. Isn’t there a simple code i van write to the command button (CheckOut) to just hide this user in de the listbox after clicking the commandbutton?
 

Anthony47

Well-known Member
Joined
Mar 29, 2006
Messages
3,243
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Assuming you can create a list of vivitors that checked in, for example in A2 and down
Assuming you can create a list of visitors that checked out, for example in B2 and down
Now you can create for example in D2 the list of visitors still in using in D2 the formula
Code:
=LET(ckIn,A2:A100,ckOut,B2:B100,UNIQUE(VSTACK(FILTER(CkIn,CkIn<>""),FILTER(ckOut,ckOut<>"")),0,TRUE))
(if you expect more than 100 visitors, then extend A100 to (say) A1000 and B100 to B1000)
This formula will return the list of those that checked In but not (yet) checked out

Now you can create a named range, via Menu /Formula /Name manager:
-call it IN
-in the Refers to box insert the formula =Sheet1!$D$2# (use the correct sheet name, but use the shown syntax)

Now you can use the named range IN as the "RowSource" property for the Listbox you are talking about

On my test sheet it look like this:
Cartel1
ABCD
1CheckdInCheckedOutStll In
2AlfaCharlieAlfa
3BravoBravo
4CharlieDelta
5DeltaEco
6EcoLuis
7Luis
8
9
Sheet1
Cell Formulas
RangeFormula
D2:D6D2=LET(CkIn,A2:A100,ckOut,B2:B100,UNIQUE(VSTACK(FILTER(CkIn,CkIn<>""),FILTER(ckOut,ckOut<>"")),0,TRUE))
Dynamic array formulas.
 

Forum statistics

Threads
1,181,633
Messages
5,931,098
Members
436,775
Latest member
Taproot007

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top