Save/Retrieve Button

[Nichole09]

Hello Everyone,


I am hoping to talk this idea through and hopefully get a working vba code to accomplish this or see if I can get some maybe better ideas?

I have a template I created for work that has a series of checkboxes, text boxes and cells for data entry for each file I receive (roughly 10-15 files at a time). Instead of having 10-15 worksheets that are identical other then the name of the particular file, I am hoping I can use only 1 worksheet, and have some type of save button and retrieve button that will save the progress of a particular file and retrieve that progress at a later time just by entering the name of the file.

Could a code be created for a save button, and for a retrieve button as described? Could the save button copy what was in the text boxes, what particular checkboxes are checked, and the data I filled in certain cells be saved? This idea is way over my head, so I am open to more suggestions. I would like to avoid having so many worksheets if possible.

Thank you!!

 

[Nichole09]

Wow! This looks really complex. Thank you for whipping this up. I tried these codes. The loadData seems to work, but the retrieve macro gets stuck here:

Sheets("Status of Review").Shapes(str).OLEFormat.Object.Value = Sheets("Saved").Cells(Rowcnt, Cnt).Value

I get this error: Run-Time error '1004': Unable to set the value property of the checkbox class

 

[NdNoviceHlp]

HI Nichole. Are you using the second version of the LoadData sub that I posted? On your saved sheet, what does it say for values beside the checkboxes? Does it list all of your checkboxes and all of your sheet values? It's odd that line of code fails, it's just the reverse of what loaded the data. On my testing, the code worked fine. Maybe insert this line of code before that lines that errors and see what it says.

msgbox Sheets("Saved").Cells(Rowcnt, Cnt).Value

Dave

 

[Nichole09]

Hey Dave,

So my saved sheet seems to have all the data, I don't see anything missing. All the textboxes data is there and checkboxes, and the cell data I entered. I input that line of code you provided and the message box gives me a 1 ... I have no idea what this means

 

[Nichole09]

so whats odd, is I ran the message box and it said 1 through all my checkboxes except for checkbox 138. it said -4146, then it crashes and I get the error I originally posted. I looked at that checkbox 138 I have no idea, why it saved as that number? it should be a 1 or a 2 right? Its just unchecked, its not formatted to a cell or a macro or anything.... any idea why this happened?

 

[NdNoviceHlp]

My apologies Nichole. Apparently I'm an idiot, I changed the LoadData sub but didn't make the same changes to the Retrieve sub. The -4146 is false. 1 is true... don't figure. Anyways, give this a trial (make the sheet name changes to suit). Dave

Sub RetrieveData()
Dim Sh As Shape, str As String, LastCol As Integer, Rowcnt As Integer
Dim Arr() As Variant, Cnt As Integer, Cnt2 As Integer
With Sheets("sheet2")
LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
End With
For Cnt = 1 To LastCol
If UCase(Sheets("Sheet2").Cells(1, Cnt).Value) = UCase(Sheets("Sheet1").Range("A" & 1).Value) Then
Sheets("sheet1").TextBox1.Value = Sheets("Sheet2").Cells(2, Cnt).Value
Sheets("sheet1").TextBox2.Value = Sheets("Sheet2").Cells(3, Cnt).Value
Rowcnt = 3
With Sheets("Sheet1")
For Each Sh In .Shapes
str = Sh.Name
If InStr(str, "Check Box") Then
Rowcnt = Rowcnt + 1
Sheets("Sheet1").Shapes(str).OLEFormat.Object.Value = Sheets("Sheet2").Cells(Rowcnt, Cnt).Value
End If
Next Sh
End With
Exit For
End If
Next Cnt
If Cnt = LastCol + 1 Then
MsgBox "File " & Sheets("Sheet1").Range("A" & 1).Value & " not found!"
Exit Sub
End If
Arr = Array("e3", "e4", "h3", "r3", "r4", "u3", "u4", "x3", "x4", "e26", "e28", "e30", _
"e32", "i24", "i26", "i28", "h37", "e37", "l37", "l40", "l42", "l44", "l46", "t45", "d71")
For Cnt2 = LBound(Arr) To UBound(Arr)
Sheets("Sheet1").Range(Arr(Cnt2)).Value = Sheets("Sheet2").Cells(Cnt2 + Rowcnt, Cnt).Value
Next Cnt2
End Sub

 

[Nichole09]

Man. No worries, I just appreciate your help! So this seems to work, no errors, but for some reason, all the cell data is pasting in the cell after it? For example in the array above "e4"'s data is being put into H3's cell, and H3's data in r3's cell and so on a so forth. I took out e3 on both codes because I found out I didn't need it. so both codes start with the array "e4" now. so when running the retrieve sub, it is putting a 1 in e4.... and the rest of the data in the next cells of the array. I am not sure where the 1 is coming from. You think it is recognizing a checkbox being checked somewhere else? I am not sure.

 

[Nichole09]

holy cow! I think I fixed it...

this line I think needed to be

Sheets("Sheet1").Range(Arr(Cnt2)).Value = Sheets("Sheet2").Cells(Cnt2 + Rowcnt + 1, Cnt).Value

 

[Nichole09]

Okay this is working. Thank you Dave! I have only a select view checkboxes that do run macros, one hides/unhides rows, and another will enter today's date when checked. Will these not function after running the retrieve macro? They don't appear to be after switch to a file that was retrieved.

 

[NdNoviceHlp]

Again, I should have tested that version more closely. U have arrived at the correct solution (or U could have put Rowcnt = Rowcnt + 1 before the Arr= etc. as I changed the location of the Rowcnt code in the fix and rowcnt needed to be advanced by 1) Don't add anymore checkboxes. There values will be added/retrieved with the data. Add 2 activex command buttons to the form and assign them each a macro. HTH. Dave