Posts by DawidV

DawidV · Mar 23rd 2017

Re: Listbox Initialize and Click event giving runtime error

Thanx I just posted a new thread as you suggested.

Dawid

DawidV · Mar 23rd 2017

I am trying to find the problem in my Search code. a getting the error 'Could not find the specified object'.

If the search finds the relevant record then the ListBox must be populated.

Am including my workbook as well as a printscreen of the error and where it occurs.

Thanx

DawidV · Mar 22nd 2017

Re: Listbox Initialize and Click event giving runtime error

Hi Pike

Me again.

I have made some changes to my code and now I stuck with the Search Function. I only wrote the code to search on the Force No hut am getting an error in the code.
I have tried everything that i can think of but nothing is working.

Sorry I said i will be back.

Regards

DJV

DawidV · Mar 22nd 2017

Re: Listbox Initialize and Click event giving runtime error

Morning Pike

Thanx for the changes. I can only laugh at myself. :smile: It took me a whilw to realise that I must only change the TAG No in the Properties!! I just shows my inexperience with Excel.

I am going to start on the Edit/Save today so I am definitely going to come back to you.

Thanx again with helping.

DawidV · Mar 21st 2017

Re: Listbox Initialize and Click event giving runtime error

Hi Pike

This is the code that I managed to write and it is working, but not to my satisfaction. I do not like message boxes as you saw in my code they ar not there.
I like the colour code part, works better for me.

The TextBoxes that is Comment Out is the only TextBoxes to be checked for empty. The other TextBoxes has other code and functions linked to them. e.g.

The Textboxes with same names must be populated once the the record has been saved and you use the Get Record or the Edit Record as we will be using the Surname and Force No to do that with.

My code now checks ALL TextBoxes and turns them rgbHotPink and i only want to check some of the CheckBoxes. If I use the code you helped me with I am not sure how to incorporate my validation part in the code.

Thanx again

Code

Private Function EverythingFilledIn() As Boolean
'CREATING FUNCTION TO STEAMLINE CODE TO USE DURING VALIDATION OF CONTROLS ON USERFORM
'MAKES CODE ESIER TO READ AND MORE FLEXIBLE TO MAKE CHANGES TO
'LOOP OVER ALL CONTROLS ON THE FORM INSTEAD OF ONE BY ONE




    Dim ctl As MSForms.Control ' set variable that will look at every single Control on the form be it Text, Date, ComboBox etc
    Dim EverythingMissing As Boolean
    
    EverythingFilledIn = True
    AnythingMissing = False
    
    For Each ctl In Me.Controls
        If TypeOf ctl Is MSForms.TextBox Or TypeOf ctl Is MSForms.ComboBox Then
            If ctl.Value = "" Then
            ctl.BackColor = rgbHotPink
            Controls(ctl.Name).ForeColor = rgbFireBrick
            If Not AnythingMissing Then ctl.SetFocus
            AnythingMissing = True
            EverythingFilledIn = False
            End If
        End If
    Next ctl
       
'OLD CODE USED BEFORE THE LOOP WAS IMPLIMENTED
'PROBLEM IS THAT SOME OF THE TEXTBOS MUST BE EXCLUDED AS THE ARE NOT ON TAB 1 TROUGHT TAB 4
'TAB 0 MUST BE COMPLETES AS OTHER TABS ARE COMPLETEDE
    
'       'assume everything on userform has been filled in for procedure to work properly
'        EverythingFilledIn = True
'
'        If txtDataSurname.Value = "" Then 'check if - txtDataSurname - contains an empty string
'        'if empty then change backgroud color and labelcolor
'
'            txtDataSurname.BackColor = rgbHotPink
'            lblDataSurname.ForeColor = rgbFireBrick
'            tabCtrlApplications.Value = 1 'index based, so 1 is the second tab/page, this line activates that page so you can set focus on a control on this tab.
'            'txtDataSurname.SetFocus 'sets focus on the control.
'            EverythingFilledIn = False 'if field/controle is empty BackColor will be set to Pink
'        End If
'
'        If txtDataInI.Value = "" Then 'check if - txtDataInI - contains an empty string
'        'if empty then change backgroud color and labelcolor
'            txtDataInI.BackColor = rgbHotPink
'            lblDataInI.ForeColor = rgbFireBrick
'            tabCtrlApplications.Value = 1 'index based, so 1 is the second tab/page, this line activates that page so you can set focus on a control on this tab.
'            txtDataInI.SetFocus 'sets focus on the control
'            EverythingFilledIn = False 'if field/controle is empty BackColor will be set to Pink
'        End If
'
'        If txtDataPlaceOfEnlistment.Value = "" Then 'check if - txtDataPlaceOfEnlistment - contains an empty string
'        'if empty then change backgroud color and labelcolor
'            txtDataPlaceOfEnlistment.BackColor = rgbHotPink
'            lblDataPlaceOfEnlistment.ForeColor = rgbFireBrick
'            tabCtrlApplications.Value = 1 'index based, so 1 is the second tab/page, this line activates that page so you can set focus on a control on this tab.
'            txtDataPlaceOfEnlistment.SetFocus 'sets focus on the control
'            EverythingFilledIn = False 'if field/controle is empty BackColor will be set to Pink
'        End If
'
'        If txtDataTownOfEnlistment.Value = "" Then 'check if - txtDataTownOfEnlistment - contains an empty string
'        'if empty then change backgroud color and labelcolor
'            txtDataTownOfEnlistment.BackColor = rgbHotPink
'            lblDataTownOfEnlistment.ForeColor = rgbFireBrick
'            tabCtrlApplications.Value = 1 'index based, so 1 is the second tab/page, this line activates that page so you can set focus on a control on this tab.
'            txtDataTownOfEnlistment.SetFocus 'sets focus on the control
'            EverythingFilledIn = False 'if field/controle is empty BackColor will be set to Pink
'        End If
'
'        If txtPersLastUnit.Value = "" Then 'check if - txtPersLastUnit - contains an empty string
'        'if empty then change backgroud color and labelcolor
'            txtPersLastUnit.BackColor = rgbHotPink
'            lblPersLastUnit.ForeColor = rgbFireBrick
'            tabCtrlApplications.Value = 2 'index based, so 1 is the third tab/page, this line activates that page so you can set focus on a control on this tab.
'            txtPersLastUnit.SetFocus 'sets focus on the control
'            EverythingFilledIn = False 'if field/controle is empty BackColor will be set to Pink
'        End If
'
'        If txtPersHAddr1.Value = "" Then 'check if - txtPersHAddr1 - contains an empty string
'        'if empty then change backgroud color and labelcolor
'            txtPersHAddr1.BackColor = rgbHotPink
'            lblPersHAddr1.ForeColor = rgbFireBrick
'            tabCtrlApplications.Value = 2 'index based, so 1 is the third tab/page, this line activates that page so you can set focus on a control on this tab.
'            txtPersHAddr1.SetFocus 'sets focus on the control
'            EverythingFilledIn = False 'if field/controle is empty BackColor will be set to Pink
'        End If
'
'        If txtPersHAddr2.Value = "" Then 'check if - txtPersHAddr2 - contains an empty string
'        'if empty then change backgroud color and labelcolor
'            txtPersHAddr2.BackColor = rgbHotPink
'            lblPersHAddr2.ForeColor = rgbFireBrick
'            tabCtrlApplications.Value = 2 'index based, so 1 is the third tab/page, this line activates that page so you can set focus on a control on this tab.
'            txtPersHAddr2.SetFocus 'sets focus on the control
'            EverythingFilledIn = False 'if field/controle is empty BackColor will be set to Pink
'        End If
'
'        If txtPersHCity.Value = "" Then 'check if - txtPersHCity - contains an empty string
'        'if empty then change backgroud color and labelcolor
'            txtPersHCity.BackColor = rgbHotPink
'            lblPersHCity.ForeColor = rgbFireBrick
'            tabCtrlApplications.Value = 2 'index based, so 1 is the third tab/page, this line activates that page so you can set focus on a control on this tab.
'            txtPersHCity.SetFocus 'sets focus on the control
'            EverythingFilledIn = False 'if field/controle is empty BackColor will be set to Pink
'        End If
'
'        If txtPersHPOBox.Value = "" Then 'check if - txtPersHPOBox - contains an empty string
'        'if empty then change backgroud color and labelcolor
'            txtPersHPOBox.BackColor = rgbHotPink
'            lblPersHPOBox.ForeColor = rgbFireBrick
'            tabCtrlApplications.Value = 2 'index based, so 1 is the third tab/page, this line activates that page so you can set focus on a control on this tab.
'            txtPersHPOBox.SetFocus 'sets focus on the control
'            EverythingFilledIn = False 'if field/controle is empty BackColor will be set to Pink
'        End If
'
'        If txtPersHPOCity.Value = "" Then 'check if - txtPersHPOCity - contains an empty string
'        'if empty then change backgroud color and labelcolor
'            txtPersHPOCity.BackColor = rgbHotPink
'            lblPersHPOCity.ForeColor = rgbFireBrick
'            tabCtrlApplications.Value = 2 'index based, so 1 is the third tab/page, this line activates that page so you can set focus on a control on this tab.
'            txtPersHPOCity.SetFocus 'sets focus on the control
'            EverythingFilledIn = False 'if field/controle is empty BackColor will be set to Pink
'        End If
'
'        If txtPersNOKSurname.Value = "" Then 'check if - txtPersNOKSurname - contains an empty string
'        'if empty then change backgroud color and labelcolor
'            txtPersNOKSurname.BackColor = rgbHotPink
'            lblPersNOKSurname.ForeColor = rgbFireBrick
'            tabCtrlApplications.Value = 2 'index based, so 1 is the third tab/page, this line activates that page so you can set focus on a control on this tab.
'            txtPersNOKSurname.SetFocus 'sets focus on the control
'            EverythingFilledIn = False 'if field/controle is empty BackColor will be set to Pink
'        End If
'
'        If txtPersNOKInI.Value = "" Then 'check if - txtPersNOKInI - contains an empty string
'        'if empty then change backgroud color and labelcolor
'            txtPersNOKInI.BackColor = rgbHotPink
'            lblPersNOKInI.ForeColor = rgbFireBrick
'            tabCtrlApplications.Value = 2 'index based, so 1 is the third tab/page, this line activates that page so you can set focus on a control on this tab.
'            txtPersNOKInI.SetFocus 'sets focus on the control
'            EverythingFilledIn = False 'if field/controle is empty BackColor will be set to Pink
'        End If
'
'        If txtPersNOKHAddr1.Value = "" Then 'check if - txtPersNOKHAddr1 - contains an empty string
'        'if empty then change backgroud color and labelcolor
'            txtPersNOKHAddr1.BackColor = rgbHotPink
'            lblPersNOKHAddr1.ForeColor = rgbFireBrick
'            tabCtrlApplications.Value = 2 'index based, so 1 is the third tab/page, this line activates that page so you can set focus on a control on this tab.
'            txtPersNOKHAddr1.SetFocus 'sets focus on the control
'            EverythingFilledIn = False 'if field/controle is empty BackColor will be set to Pink
'        End If
'
'        If txtPersNOKHAddr2.Value = "" Then 'check if - txtPersNOKHAddr2 - contains an empty string
'        'if empty then change backgroud color and labelcolor
'            txtPersNOKHAddr2.BackColor = rgbHotPink
'            lblPersNOKHAddr2.ForeColor = rgbFireBrick
'            tabCtrlApplications.Value = 2 'index based, so 1 is the third tab/page, this line activates that page so you can set focus on a control on this tab.
'            txtPersNOKHAddr2.SetFocus 'sets focus on the control
'            EverythingFilledIn = False 'if field/controle is empty BackColor will be set to Pink
'        End If
'
'        If txtPersNOKCity.Value = "" Then 'check if - txtPersNOKCity - contains an empty string
'        'if empty then change backgroud color and labelcolor
'            txtPersNOKCity.BackColor = rgbHotPink
'            lblPersNOKCity.ForeColor = rgbFireBrick
'            tabCtrlApplications.Value = 2 'index based, so 1 is the third tab/page, this line activates that page so you can set focus on a control on this tab.
'            txtPersNOKCity.SetFocus 'sets focus on the control
'            EverythingFilledIn = False 'if field/controle is empty BackColor will be set to Pink
'        End If
'
'         If txtPersEmployer.Value = "" Then 'check if - txtPersEmployer - contains an empty string
'        'if empty then change backgroud color and labelcolor
'            txtPersEmployer.BackColor = rgbHotPink
'            lblPersEmployer.ForeColor = rgbFireBrick
'            tabCtrlApplications.Value = 2 'index based, so 1 is the third tab/page, this line activates that page so you can set focus on a control on this tab.
'            txtPersEmployer.SetFocus 'sets focus on the control
'            EverythingFilledIn = False 'if field/controle is empty BackColor will be set to Pink
'        End If
'
'         If txtPersCivilianOccupation.Value = "" Then 'check if - txtPersCivilianOccupation - contains an empty string
'        'if empty then change backgroud color and labelcolor
'            txtPersCivilianOccupation.BackColor = rgbHotPink
'            lblPersCivilianOccupation.ForeColor = rgbFireBrick
'            tabCtrlApplications.Value = 2 'index based, so 1 is the third tab/page, this line activates that page so you can set focus on a control on this tab.
'            txtPersCivilianOccupation.SetFocus 'sets focus on the control
'            EverythingFilledIn = False 'if field/controle is empty BackColor will be set to Pink
'        End If
'
'        If txtPersCivilianOccupation.Value = "" Then 'check if - txtPersCivilianOccupation - contains an empty string
'        'if empty then change backgroud color and labelcolor
'            txtPersCivilianOccupation.BackColor = rgbHotPink
'            lblPersCivilianOccupation.ForeColor = rgbFireBrick
'            tabCtrlApplications.Value = 2 'index based, so 1 is the third tab/page, this line activates that page so you can set focus on a control on this tab.
'            txtPersCivilianOccupation.SetFocus 'sets focus on the control
'            EverythingFilledIn = False 'if field/controle is empty BackColor will be set to Pink
'        End If
'
'        If txtSAPDMemo.Value = "" Then 'check if - txtSAPDMemo - contains an empty string
'        'if empty then change backgroud color and labelcolor
'            txtSAPDMemo.BackColor = rgbHotPink
'            lblSAPDMemo.ForeColor = rgbFireBrick
'            tabCtrlApplications.Value = 3 'index based, so 1 is the third tab/page, this line activates that page so you can set focus on a control on this tab.
'            txtSAPDMemo.SetFocus 'sets focus on the control
'            EverythingFilledIn = False 'if field/controle is empty BackColor will be set to Pink
'        End If
'
'        If txtCHAAppMemo.Value = "" Then 'check if - txtCHAAppMemo - contains an empty string
'        'if empty then change backgroud color and labelcolor
'            txtCHAAppMemo.BackColor = rgbHotPink
'            lblCHAAppMemo.ForeColor = rgbFireBrick
'            tabCtrlApplications.Value = 4 'index based, so 1 is the third tab/page, this line activates that page so you can set focus on a control on this tab.
'            txtCHAAppMemo.SetFocus 'sets focus on the control
'            EverythingFilledIn = False 'if field/controle is empty BackColor will be set to Pink
'        End If
'
'        If txtCHAInterventionsMemo.Value = "" Then 'check if - txtCHAInterventionsMemo - contains an empty string
'        'if empty then change backgroud color and labelcolor
'            txtCHAInterventionsMemo.BackColor = rgbHotPink
'            lblCHAInterventionsMemo.ForeColor = rgbFireBrick
'            tabCtrlApplications.Value = 4 'index based, so 1 is the third tab/page, this line activates that page so you can set focus on a control on this tab.
'            txtCHAInterventionsMemo.SetFocus 'sets focus on the control
'            EverythingFilledIn = False 'if field/controle is empty BackColor will be set to Pink
'        End If
'
'        If txtCHADivision.Value = "" Then 'check if - txtCHADivision - contains an empty string
'        'if empty then change backgroud color and labelcolor
'            txtCHADivision.BackColor = rgbHotPink
'            lblCHADivision.ForeColor = rgbFireBrick
'            tabCtrlApplications.Value = 4 'index based, so 1 is the third tab/page, this line activates that page so you can set focus on a control on this tab.
'            txtCHADivision.SetFocus 'sets focus on the control
'            EverythingFilledIn = False 'if field/controle is empty BackColor will be set to Pink
'        End If
'
'        If txtCHAPost.Value = "" Then 'check if - txtCHAPost - contains an empty string
'        'if empty then change backgroud color and labelcolor
'            txtCHAPost.BackColor = rgbHotPink
'            lblCHAPost.ForeColor = rgbFireBrick
'            tabCtrlApplications.Value = 4 'index based, so 1 is the third tab/page, this line activates that page so you can set focus on a control on this tab.
'            txtCHAPost.SetFocus 'sets focus on the control
'            EverythingFilledIn = False 'if field/controle is empty BackColor will be set to Pink
'        End If

Display More

DawidV · Mar 21st 2017

Re: Listbox Initialize and Click event giving runtime error

Hi Pike

I love ya man!!! It is working much better now and my code is much cleaner/leaner as well!!!! Really appreciate it!!

I wrote a piece of code

Code

Private Function EverythingFilledIn() As Boolean

to check if all text boxes are filled in before user can save. I did that with the help of YouTube, books and the Forums. It is now checking for empty TextBoxes on the whole userform but it must only check specific TextBoxes. In my original code that I attached I left it below the Loop. The idea was to comment the Loop out and then use the other code but i think it is possible to only check specific TextBoxes with the loop. I just do not know how to do that. I posted a question a while back asking for help with this but received no response up to now.

As you see with my userform I would like to use the Listbox to Edit and delete records once we start using the userform. If I hide some of the columns in my ListBox as you suggest in Post 4 will I still be able to do that, e.g. Edit/Delete by double clicking on a row to fill the userform to Edit/Delete?

This is a serious work in progress, I am still busy with the other buttons and with the help of YouTube, books and the Forums I will get there eventually.

Thanx again for the serious help with this.

I really appreciate it.

DawidV · Mar 21st 2017

Re: Listbox Initialize and Click event giving runtime error

Hi Pike

Said I am doing something stupid here.

I understand the part about the Sheets Activate. thanx it is working now by adding the scrollbar.

My question about the "Textbox" is the following. As you can see in my code I give the TextBoxes proper names starting with "txt". How will I then sort the problem in the error part?

As the code is now set up it takes the columns from A to U and place them in the Listbox. I however only want to place 21 columns in the Listbox but they are of my choosing and not necessary from A to U. how will I do that?

Thanx

DawidV · Mar 21st 2017

Hi All

I am having a serious problem with getting the listbox to populate properly. I know I am doing something stupid here but i am looking over it.

I am trying to populate my Listbox with the 21 fields and as in the Initialize Event. Only 11 is showing and I do not want to include the numbers on the left.
I know how to set the width correctly but if I understand the listbox correctly a scrollbar mus appear at the bottom of the Listbox so that I can scroll Left and Right.

I am including a print screen with the error as well as my code.

This is a serious work in progress still.

DawidV · Mar 11th 2017

Re: SetFocus to an empty textbox control on a Multipage UserForm

Thanx i will do that and again many thsnax for the help.
really appreciate it

DJV

DawidV · Mar 11th 2017

Re: SetFocus to an empty textbox control on a Multipage UserForm

Carim

I got it to work now!!!

I added the following line of code after the last End If

Code

If Not EverythingFilledIn Then Exit Sub

It now changes the controls and labels as I want it to do.

Last help on this part of the code now is:

Once there are empty controls and you get to the last Tab and after jou Click the Save Button how do i get the focus to go back to the first TAB that has an empty control on

Than again for your help.

DJV

DawidV · Mar 11th 2017

Re: SetFocus to an empty textbox control on a Multipage UserForm

Hi Carim

I take it you are talking about the Private Sub cmdSave_Click() event here.

i am VERY new to this and do not understand the recommendation on what you suggest i do.

Would you terribly mind to do and explain it in my code and send back to me. Just do the first textbox on Tab 2 txtDataSurname.

Sorry for the inconvenience. i know it is Saturday afternoon/weekend .... :0ops::0ops::0ops:

DawidV · Mar 11th 2017

I am making a Multipage userform with 5 Tabs. I need to check that all controls on the userform is completed before it is safed.

I have Text,Combo & Dates on the Tabs. I have managed to set defaults to the Combo and Date Controls by means of the Private Sub UserForm_Initialize() procedure.

I am trying to loop through the userform to confirm that all controls are completed and once that is done to change the BackColor of the controls that is empty and to get the cursor to SetFocus on the FIRST control that is empty.

Plse I need help with this.

thanx

DawidV · Mar 8th 2017

Re: Using text and numbers in same textbox

THANX

Code is working now

DawidV · Mar 8th 2017

I am using the following code to populate textbox with uppercase which is working fine. The txtPersHPOBox field however has to take numbers as well as text.

I need help with modifying the code to accept both.

Code

Private Sub txtPersHPOBox_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)




'TO FORCE USER TO ONLY TYPE TEXT INTO A TEXTBOX
'TO CHANGE ALL TEXT IN A TEXTBOX TO UPPERCASE




    Select Case KeyAscii
        Case Is < Asc("0")
            Rem do nothing
        Case Is <= Asc("9")
            KeyAscii = 0
        Case Is < Asc("a")
            Rem do nothing
        Case Is <= Asc("z")
            KeyAscii = KeyAscii - (Asc("a") - Asc("A"))
        Case Else
            Rem do nothing
    End Select
    If KeyAscii = 0 Then Beep
End Sub

Display More

Thanx

DawidV · Feb 27th 2017

Re: Show and Hide of UserForm and Excel Worksheet with Toggle Button and Command Butt

Yes.

Thanx i got it sorted. It is working now. Sorry misread your question

DawidV · Feb 27th 2017

Re: Show and Hide of UserForm and Excel Worksheet with Toggle Button and Command Butt

The users in the office will only use the Application. Not seeing the Excel Sheet at at all. once they have added the applicants i will then work with the WS. To make certian all validations has been done and that there are not duplicates etc.

Idee will be to do aal this while they are using the Application but i am not there yet with writing VBA Code.

As you saw in the workbook some of the stuff I have done but it will take to long to do all and then give them the Application.

Currently they are typing it directly into a Excel worksheet and it is chaos.

I will be coming back a lot to the forum as i carry on with this.

Thanx again.

DawidV · Feb 26th 2017

Re: Show and Hide of UserForm and Excel Worksheet with Toggle Button and Command Butt

Ok. Got that.

Thanx for the help so far . I really appreciate it.

DawidV · Feb 25th 2017

Hi
I am currently having problems to Show and Hide UserForm and worksheet properly. When I open the workbook everything works fine. The Userorm opens and the Worksheet is hidden as I want it. When I click the Show Worksheet the worksheet appears. However when I click the Close UserForm the UserForm and Worksheet disappears completely!

I am very new to VBA.

Here is the code I am using:

HTML

Private Sub tglDashShowSheet_Click()
          'TOGGLE BUTTON ON DASHBOARD - TO SHOW WORKSHEET - WHEN EXCEL OPENS ONLY THE USERFORM WILL
          'VISIBLE - EXCEL SPREADSHEET IS HIDDEN
          If tglDashShowSheet.Value = False Then
          'VISIBLE IS SET TO TRUE IN PROPERTIES WINDOW
                    Application.Visible = False
                    tglDashShowSheet.BackColor = &H80FF&
          End If
          'IF SPREADSHEET OPTION IS SELECTED THE BUTTON BECOMES INACTIVE
          If tglDashShowSheet.Value = True Then
                    Application.Visible = True
                    tglDashShowSheet.BackColor = &H80FF&
          End If
 
End Sub
Private Sub cmdDashCloseForm_Click() 'HIDE USERFORM - DASHBOARD BUTTON
          
          ApplicationsData.Hide
                    
End Sub

Display More

Thanx

Posts by DawidV

Listbox Initialize and Click event giving runtime error

Could not find the specified object

Listbox Initialize and Click event giving runtime error

Listbox Initialize and Click event giving runtime error

Listbox Initialize and Click event giving runtime error

Listbox Initialize and Click event giving runtime error

Listbox Initialize and Click event giving runtime error

Listbox Initialize and Click event giving runtime error

SetFocus to an empty textbox control on a Multipage UserForm

SetFocus to an empty textbox control on a Multipage UserForm

SetFocus to an empty textbox control on a Multipage UserForm

SetFocus to an empty textbox control on a Multipage UserForm

Using text and numbers in same textbox

Using text and numbers in same textbox

Show and Hide of UserForm and Excel Worksheet with Toggle Button and Command Button

Show and Hide of UserForm and Excel Worksheet with Toggle Button and Command Button

Show and Hide of UserForm and Excel Worksheet with Toggle Button and Command Button

Show and Hide of UserForm and Excel Worksheet with Toggle Button and Command Button