@Rick Rothstein thank you for the offer to look at the code. You have to promise not to laugh at my noobish coding! Understand I'm not a programmer. Well, that will be obvious, won't it?!?! this is just for my personal use.
Option Explicit
Dim ctl As Control 'used to go through and clear out "RonsAlbums" form for new record
Dim LR As Integer 'get last row of albums
Dim intRowNo As Integer 'new row to put new album in
Dim intRecNo As Integer 'database record number
Private Sub UserForm_Initialize()
ClearAlbumForm 'empty all the ****
LR = Range("A65536").End(xlUp).Row 'the last row of albums
LoadEmUp 'load up the comboboxes and the rec# of this album
End Sub
Private Sub cmdSaveIt_Click()
intRowNo = LR + 1 'the row the changed album info to go in
'MsgBox intRowNo
Range("a" & intRowNo) = TextBox1 'artist
Range("b" & intRowNo) = TextBox2 'album name
Range("c" & intRowNo) = TextBox3 'year
Range("d" & intRowNo) = ComboBox3 'genre from the combobox
Range("e" & intRowNo) = ComboBox1 'format from the combobox
Select Case ComboBox2.ListIndex 'condition of vinyl DOWN THE ROAD IF FORMAT IS NOT VINYL I SHOULD NOT HAVE THESE CHOICES???
Case 0
Range("f" & intRowNo) = "M"
Case 1
Range("f" & intRowNo) = "NM"
Case 2
Range("f" & intRowNo) = "VG+"
Case 3
Range("f" & intRowNo) = "E"
Case 4
Range("f" & intRowNo) = "VG"
Case 5
Range("f" & intRowNo) = "G"
Case 6
Range("f" & intRowNo) = "G+"
Case 7
Range("f" & intRowNo) = "G-"
Case 8
Range("f" & intRowNo) = "P"
Case 9
Range("f" + intRowNo) = "F"
Case 10
Range("f" + intRowNo) = "S"
End Select
Range("g" & intRowNo) = TextBox6 'matrix which is the name like on discogs
Range("h" & intRowNo) = TextBox7 'ething from the run out
Range("i" & intRowNo) = TextBox5 'notes
If CheckBox1.Value = True Then
Range("j" & intRowNo) = "X"
Else
Range("j" & intRowNo) = ""
End If
Range("k" & intRowNo) = TextBox4 'original position
Unload RonsAlbums
End Sub
Sub ClearAlbumForm()
For Each ctl In RonsAlbums.Controls 'clears out every
Select Case TypeName(ctl)
Case "TextBox"
ctl.Text = ""
Case "ComboBox"
ctl.ListIndex = -1
ctl.Value = ""
End Select
Next ctl
End Sub
Private Sub LoadEmUp()
'load the comboboxes choices and the record #?
ComboBox1.AddItem "Vinyl"
ComboBox1.AddItem "CD"
ComboBox1.AddItem "Cassette"
ComboBox1.AddItem "Stream"
ComboBox1.ListIndex = 0
ComboBox2.AddItem "Mint (M)" 'load the choices of condition
ComboBox2.AddItem "Near Mint (NM)"
ComboBox2.AddItem "Very Good+ )VG+)"
ComboBox2.AddItem "Excellent (E)"
ComboBox2.AddItem "Very Good (VG)"
ComboBox2.AddItem "Good (G)"
ComboBox2.AddItem "Good+ (G+)"
ComboBox2.AddItem "Good- (G-)"
ComboBox2.AddItem "Poor (P)"
ComboBox2.AddItem "Fair (F)"
ComboBox2.AddItem "Sealed (S)"
ComboBox2.ListIndex = 2
ComboBox3.AddItem "Blues" 'load the choices for genre Somehow need to be able to add to this on the fly
ComboBox3.AddItem "Rock"
ComboBox3.AddItem "Jazz"
ComboBox3.AddItem "Pop Yuck"
ComboBox3.AddItem "Country"
ComboBox3.AddItem "Progressive"
ComboBox3.AddItem "Other"
ComboBox3.ListIndex = 1
intRecNo = Range("k" & LR).Value + 1 'yes! The new rec# is previous rec +1
TextBox4.Value = intRecNo 'new album database rec#
End Sub