[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Visual Basic Help

Posted on 2010-01-04
8
Medium Priority
?
286 Views
Last Modified: 2013-11-25
Hi,
I posted a question a few weeks ago which was answered brilliantly by 'VipulKadia'.
I have created a visitor signing in/out system form that information, however it has slight problem before i can declare it finished.

The basics are, you sign in using the userform, this fills in values on sheet2, when you sign out a list of people who have signed in on the current date and have the value 'TRUE' are shown. selecting one and clicking sign out changes their value to false.

However, in order for this to work the userform needs to 'end' rather than 'endsub'. If i use the command 'end' then the 'userformstart' needs to be opened again.
i need it to go back to userformstart after the user signsout.

Hope that makes sense, Many Thanks
0
Comment
Question by:Didsi
  • 4
  • 4
8 Comments
 

Author Comment

by:Didsi
ID: 26170857
sorry, forgot to attach file!
Visitor-System-V6.xls
0
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 2000 total points
ID: 26170951
You should never use End in code. You need something like this in the signin userform:

Private Sub ButtonSignout_Click()
    Dim frm As UserformSignout
    Set frm = New UserformSignout
    Me.Hide
    frm.Show
    Unload frm
    Me.Show
End Sub


then in the signout form, the code would be as below. Note that your system doesn't work properly if you have two people with the same surname.

Private Sub UserForm_Initialize()
    Dim ShObj As Worksheet
    Dim FirstRow As Long
    Dim LastRow As Long
    Dim RowIndex As Long
    
    Set ShObj = ThisWorkbook.Worksheets(2)
    FirstRow = 2
    LastRow = ShObj.Cells.SpecialCells(xlCellTypeLastCell).Row
    
    For RowIndex = FirstRow To LastRow
        If UCase(ShObj.Cells(RowIndex, 10).Text) = "TRUE" Then
            If DateDiff("d", Format(CDate(ShObj.Cells(RowIndex, 7).Text), "dd-MM-yyyy"), Format(Now, "dd-MM-yyyy")) = 0 Then
                lstusers.AddItem ShObj.Cells(RowIndex, 1).Text & ", " & ShObj.Cells(RowIndex, 2).Text
            End If
        End If
    Next
    
End Sub


Private Sub cmdSignOut_Click()
    
        Dim RowIndex As Long
    Dim DataArr
    Dim RowNum As Long
    Dim ShObj As Worksheet
    
    Set ShObj = ThisWorkbook.Worksheets(2)
    
    For RowIndex = 0 To lstusers.ListCount - 1
        If lstusers.Selected(RowIndex) Then
            DataArr = Split(lstusers.List(RowIndex), ",")
            
            RowNum = ShObj.Columns("A:A").Find(DataArr(0)).Row
            
            ShObj.Cells(RowNum, 10) = "False"
            ShObj.Cells(RowNum, 9) = Time$
        End If
    Next
    
    Me.Hide

End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    Me.Hide
End Sub

Open in new window

0
 

Author Comment

by:Didsi
ID: 26171072
that shows the form, applies signout, but does not close the userformsignout
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 85

Expert Comment

by:Rory Archibald
ID: 26171110
It should do. The code in the button click hides it, returning control to the calling routine, which then unloads it. Is that not happening for you? If not, please upload the file as you have implemented my code.
Regards,
Rory
0
 

Author Closing Comment

by:Didsi
ID: 31672365
Sorry, i had missed a bit!
That has worked, could you please briefly explain what that code does!!

I didnt realise there would be an issue with same surnames, would you mind suggesting a solution!, if you would like i will post a new question with points availbel.
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 26171344
I thought I just had explained what it did - can you specify which bit you need clarifying? :)
0
 

Author Comment

by:Didsi
ID: 26171368
so you did, sorry! not on top form today!!! Thanks, I have posted a new question regarding the problem you found. http://www.experts-exchange.com/Programming/Languages/Visual_Basic/Q_25017543.html
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 26171409
Just responded there! ;)
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question