Userform Code Errors (broken by novice/fool)

Hi All,

Have only been using VBA for about a month and in all honesty most of the time I am completely lost and muddle my way through by trial and error looking at all the forum posts and add, alter, copy and delete code to suit (often in error due to ignorance). Unfortunately I am one of those people that decides to learn something new and jumps in with both feet and a complete lack of knowledge about what they are doing ... on the plus side, you guys get to show people like me the knowledge to strive for !

Long story short, I created the attached workbook with a Userform to add new records and view and update existing records. Initially it worked fine and on opening with the "Update Records" button it would open the Userform with blank fields and on clicking the spin button would go to the first (or last) record which could then be updated. Unfortunately now however, I have done something to the code that effectively leaves the Userform broken. Ideally it would jump to the first saved record when opening and the add, update buttons etc would all work as intended, now they don't.

If anyone knows how to fix the attached would be very much appreciated ... even better if you could tell me what I did wrong !

Thanks for taking the time to read !

Hello D.A.S.,

I just starting looking at your workbook. I finally stop laughing after seeing your "Help Form" message. I give it a score of 10!

Hello D.A.S.,

Add this code to your UserForm1 code. This will display the first record when the form is displayed.

Private Sub UserForm_Activate()
    SpinButton1.Value = 2
    TextBox7.Value = SpinButton1.Value
    ShowCurrentRecord
End Sub

Hi Leith,

I added the code to the UserForm1 code but now brings up the following error when the form is opened:

Sub ShowCurrentRecord()

  Dim Wks As Worksheet
  
    Set Wks = ThisWorkbook.Worksheets(1)
   
    With UserForms(0)
   R = .SpinButton1.Value + 1
      .txtContName = Wks.Cells(R, "B").Value
      .txtClientFirstName = Wks.Cells(R, "C").Value
      .txtClientLastName = Wks.Cells(R, "D").Value
      .txtIntContDat = Wks.Cells(R, "E").Value
      .txtIntAssessDat = Wks.Cells(R, "F").Value
      .txtIntContComp = Wks.Cells(R, "G").Value
      .ComboContType = Wks.Cells(R, "H").Value
      .txtDisCost = Format(Wks.Cells(R, "I").Value, "Currency")
      .txtremCost = Format(Wks.Cells(R, "J").Value, "Currency")
      .txtTCSent.Value = Wks.Cells(R, "K").Value
      .txtTCRec.Value = Wks.Cells(R, "L").Value
      .ComboContStatus.Value = Wks.Cells(R, "M").Value
      .txtSiteUnitNo.Value = Wks.Cells(R, "N").Value
      .txtSiteStAdrs.Value = Wks.Cells(R, "O").Value
      .txtSiteSuburb.Value = Wks.Cells(R, "P").Value
      .txtSiteCity.Value = Wks.Cells(R, "Q").Value
      .txtPostUnitNo.Value = Wks.Cells(R, "R").Value
      .txtPostStAdrs.Value = Wks.Cells(R, "S").Value
      .txtSiteSuburb.Value = Wks.Cells(R, "T").Value
      .txtPostCity.Value = Wks.Cells(R, "U").Value
      .txtHomePh.Value = Wks.Cells(R, "V").Value
      .txtWorkPh.Value = Wks.Cells(R, "W").Value
      .txtMobPh.Value = Wks.Cells(R, "X").Value
      .txtEmail.Value = Wks.Cells(R, "Y").Value
      .txtPrinDis.Value = Wks.Cells(R, "Z").Value
      .txtPrinRem.Value = Wks.Cells(R, "AA").Value
      .txtAdCont1FNam.Value = Wks.Cells(R, "AB").Value
      .txtAdCont1LNam.Value = Wks.Cells(R, "AC").Value
      .txtAdCont1HomePh.Value = Wks.Cells(R, "AD").Value
      .txtAdCont1WorkPh.Value = Wks.Cells(R, "AE").Value
      .txtAdCont1MobPh.Value = Wks.Cells(R, "AF").Value
      .txtAdCont1Email.Value = Wks.Cells(R, "AG").Value
      .txtAdCont2FNam.Value = Wks.Cells(R, "AH").Value
      .txtAdCont2LNam.Value = Wks.Cells(R, "AI").Value
      .txtAdCont2HomePh.Value = Wks.Cells(R, "AJ").Value
      .txtAdCont2WorkPh.Value = Wks.Cells(R, "AK").Value
      .txtAdCont2MobPh.Value = Wks.Cells(R, "AL").Value
      .txtAdCont2Email.Value = Wks.Cells(R, "AM").Value
      .txtAdCont3FNam.Value = Wks.Cells(R, "AN").Value
      .txtAdCont3LNam.Value = Wks.Cells(R, "AO").Value
      .txtAdCont3HomePh.Value = Wks.Cells(R, "AP").Value
      .txtAdCont3WorkPh.Value = Wks.Cells(R, "AQ").Value
      .txtAdCont3MobPh.Value = Wks.Cells(R, "AR").Value
      .txtAdCont3Email.Value = Wks.Cells(R, "AS").Value
      .txtAdCont4FNam.Value = Wks.Cells(R, "AT").Value
      .txtAdCont4LNam.Value = Wks.Cells(R, "AU").Value
      .txtAdCont4HomePh.Value = Wks.Cells(R, "AV").Value
      .txtAdCont4WorkPh.Value = Wks.Cells(R, "AW").Value
      .txtAdCont4MobPh.Value = Wks.Cells(R, "AX").Value
      .txtAdCont4Email.Value = Wks.Cells(R, "AY").Value
      .txtNotes.Value = Wks.Cells(R, "AZ").Value
      .TextBox7.Value = .SpinButton1.Value
    End With

End Sub

In the above " R = .SpinButton1.Value + 1 " is highlighted when I debeg ....Thoughts?

Hello D.A.S.,

Do you have more than one UserForm loaded at a time? If so you need to change your this line...

    With UserForms(0)

To match the UserForm you want the macro to examine. UserForms(0) points to the first UserForm was loaded. If that UserForm loads another one, then UserForms(1) refers to the second form and so on.

Hello D.A.S.,

You are not taking up my time. I am here to answer questions. Post back with any questions or problems you have.

Hello Darryl,

You can raise a pint for me and say "Sláinte!". That will do just fine.

Hello Darryl,

The Max property of any SpinButton defaults to 100. You can change this property anytime you like.

    SpinButton1.Max = 160

Hello Darryl,

Tapadh leat mo caraid!


I think Robert Burns said it the best...

And there’s a hand, my trusty fiere !
and gie's a hand o’ thine !
And we’ll tak a right gude-willy waught,
for auld lang syne.