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

[excelstudentnl]

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

 

[cmowla]

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]

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]

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

=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
	A	B	C	D
1	CheckdIn	CheckedOut		Stll In
2	Alfa	Charlie		Alfa
3	Bravo			Bravo
4	Charlie			Delta
5	Delta			Eco
6	Eco			Luis
7	Luis
8
9
Sheet1

Cell Formulas
Range	Formula
D2:D6	D2	=LET(CkIn,A2:A100,ckOut,B2:B100,UNIQUE(VSTACK(FILTER(CkIn,CkIn<>""),FILTER(ckOut,ckOut<>"")),0,TRUE))
Dynamic array formulas.