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!!

 

[NdNoviceHlp]

So what do U want to do.. transfer the worksheets from all files to one workbook, transfer data from all the workbooks to 1 workbook or something else? Please be clear re. worksheets, files and workbooks. A file contains a workbook. A workbook contains worksheets. Files and worksheets are not the same. You can save your current changes to a file at any time (temporary by moving it to a new location and not saving it or permanently by saving the file). You can retrieve any data as long as it is temporarily stored or saved. Data can also be imported from any excel file as long as it's not open. Anyways, HTH. Good luck. Dave

 

[Nichole09]

Hi Dave,

 

[Nichole09]

Hi Dave,


Thank you for your response. I am sorry I was not clear on my post. What I want, is a save and retrieve macro and after thinking about it, a delete macro. I have only 1 workbook and 1 worksheet (worksheet1) I want to work out of for each file. ( I am referring to files at my work, not in regards to excel files. Example “Anderson” file, or “Smith” File. I would be typing these file names into cell A1)

Save Button: I want this macro to copy the data in worksheet1. I want it to copy or save the exact checkboxes checked, data entered in cells, and text entered in each text box so I can pull up this data at a later time by hitting a “retrieve button”.

Retrieve button: I want this macro to pull up, or paste the exact data I entered the last time I ran the “save” macro. For example, if I were to type my file name “Smith” in A1 and run the retrieve macro, the exact checkboxes and text entered in textboxes would pull up or populate based on the last time I ran the save macro on this particular name entered in A1.

Delete Button: I would also like to have a delete button, so If I were to finish my work on the Smith file, I can hit delete, and everything saved/copied last from running the save macro can be deleted or removed.

So these three buttons, would allow me to work out of 1 workbook and 1 worksheet for each file I have. Instead of having 10 to 15 different worksheets for each file name. I am hoping with these three macros, I can run all my data through only 1 worksheet. I know this is a really complicated request, hopefully this is possible, and if not, based on my description maybe there might be a better solution. The goal is not to have so many worksheets, since worksheet1 will be identical on each new worksheet except for the data being entered.

 

[NdNoviceHlp]

" I am referring to files at my work, not in regards to excel files. Example “Anderson” file, or “Smith” File." What kind of files are they if they are not excel files? Dave

 

[Nichole09]

actual paper files and the way I identify them is by their last names. Once I get these paper files, I go to my excel worksheet and start doing numerous tasks on them, which I labelled checkboxes for so I know what I have done on a file when I come back to it...

 

[NdNoviceHlp]

Ok I think I sort of understand what U are doing. Where do U want to store the data re. filename, checkbox status, textbox input, and cell data? I would suggest adding a listbox to your sheet that U could use to load names into. U could enter name in a textbox select the add button to add a name to the listbox. When U select a name in the listbox it could retrieve the info. U could have a delete button to delete the selected name/data. U will need a save button to transfer the info to wherever U want to store it... maybe sheet2? Please also describe how many textboxes, checkboxes, and cell locations U use. Are they forms, active X, grouped together? Dave

 

[Nichole09]

okay, awesome! Is it possible to avoid a listbox? I would like to be able to simply type in the name of the file in A1 and just run the macro to select the file. There could be a code in there that says the "file does not exist" if I don't have a file with that name or if I spelt it wrong perhaps. Is it possible to simply just type in the name instead of selecting the file through a listbox? Or if this is the only way or best way to do this, could it be hidden? maybe on sheet 2. for example entering file name in A1, and hitting the save button would add it to the listbox in sheet2 without the user seeing it?

I could store the data in another sheet that works, I just want to limit the number of sheets I have as much as possible.

I have, 97 checkboxes (all form control), 2 textboxes, I have 35 different cells that data will be entered too that will need to be saved. cells: A1 (file name will go here), 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

Thank you!!!

 

[NdNoviceHlp]

This seems to work if I understand your needs. Give it a trial. Sheet code requires Sheet2 exists. Dave

Sub LoadData()
Dim Sh As Shape, str As String, LastCol As Integer, Rowcnt As Integer
Dim Arr() As Variant, Cnt As Integer, Cnt2 As Integer, ChechCnt As Integer
With Sheets("sheet2")
LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
End With
'no data
If Sheets("Sheet2").Cells(1, 1).Value = 0 Then
LastCol = 0
End If
'file already exists, Replace data
For checkcnt = 1 To LastCol
If Sheets("Sheet2").Cells(1, checkcnt).Value = Sheets("Sheet1").Range("A" & 1).Value Then
LastCol = checkcnt - 2
End If
Next checkcnt
Sheets("Sheet2").Cells(1, LastCol + 1).Value = "FileName"
Sheets("Sheet2").Cells(1, LastCol + 2).Value = Sheets("Sheet1").Range("A" & 1).Value
Sheets("Sheet2").Cells(2, LastCol + 1).Value = "TextBox1"
Sheets("Sheet2").Cells(2, LastCol + 2).Value = Sheets("sheet1").TextBox1.Value
Sheets("Sheet2").Cells(3, LastCol + 1).Value = "TextBox1"
Sheets("Sheet2").Cells(3, LastCol + 2).Value = Sheets("sheet1").TextBox1.Value
Rowcnt = 3
With Sheets("Sheet1")
For Each Sh In .Shapes
Rowcnt = Rowcnt + 1
str = Sh.Name
If InStr(str, "Check Box") Then
Sheets("Sheet2").Cells(Rowcnt, LastCol + 1).Value = str
Sheets("Sheet2").Cells(Rowcnt, LastCol + 2).Value = Sheets("Sheet1").Shapes(str).OLEFormat.Object.Value
End If
Next Sh
End With
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("Sheet2").Cells(Cnt2 + Rowcnt, LastCol + 1).Value = Arr(Cnt2)
Sheets("Sheet2").Cells(Cnt2 + Rowcnt, LastCol + 2).Value = Sheets("Sheet1").Range(Arr(Cnt2)).Value
Next Cnt2
End Sub

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
Rowcnt = Rowcnt + 1
str = Sh.Name
If InStr(str, "Check Box") Then
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

To operate, put your file name in sheet1 A1 and run the LoadData sub. To retrieve the file data, put file name in Sheet1 A1 and run the RetrieveData sub. There is no delete file but there are many many columns available to store data.

 

[NdNoviceHlp]

This sub needed some adjustment. Dave

Sub LoadData()
Dim Sh As Shape, str As String, LastCol As Integer, Rowcnt As Integer
Dim Arr() As Variant, Cnt As Integer, Cnt2 As Integer, ChechCnt As Integer
With Sheets("sheet2")
LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
End With
'no data
If Sheets("Sheet2").Cells(1, 1).Value = 0 Then
LastCol = 0
End If
'file already exists, Replace data
For checkcnt = 1 To LastCol
If Sheets("Sheet2").Cells(1, checkcnt).Value = Sheets("Sheet1").Range("A" & 1).Value Then
LastCol = checkcnt - 2
End If
Next checkcnt
Sheets("Sheet2").Cells(1, LastCol + 1).Value = "FileName"
Sheets("Sheet2").Cells(1, LastCol + 2).Value = Sheets("Sheet1").Range("A" & 1).Value
Sheets("Sheet2").Cells(2, LastCol + 1).Value = "TextBox1"
Sheets("Sheet2").Cells(2, LastCol + 2).Value = Sheets("sheet1").TextBox1.Value
Sheets("Sheet2").Cells(3, LastCol + 1).Value = "TextBox1"
Sheets("Sheet2").Cells(3, LastCol + 2).Value = Sheets("sheet1").TextBox1.Value
Rowcnt = 3
With Sheets("Sheet1")
For Each Sh In .Shapes
str = Sh.Name
If InStr(str, "Check Box") Then
Rowcnt = Rowcnt + 1
Sheets("Sheet2").Cells(Rowcnt, LastCol + 1).Value = str
Sheets("Sheet2").Cells(Rowcnt, LastCol + 2).Value = Sheets("Sheet1").Shapes(str).OLEFormat.Object.Value
End If
Next Sh
End With
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("Sheet2").Cells(Cnt2 + Rowcnt + 1, LastCol + 1).Value = Arr(Cnt2)
Sheets("Sheet2").Cells(Cnt2 + Rowcnt + 1, LastCol + 2).Value = Sheets("Sheet1").Range(Arr(Cnt2)).Value
Next Cnt2
End Sub