vba - logical error/for loop not functioning within conditional statement -

edit i realize code run every time button either checked or un-checked. need way may array values equal values column f when box first checked, , stay put, then, if box unchecked, need array values first instance of code being run printed (now empty) column f. possible? perhaps sort of global array variable?

i have bit of vba code wrote, myself, of , lot of research. code attached check box button in excel sheet.

when excel check-box button checked, 3 columns deleted. when button un-checked, columns put back. 2 of columns simple columns contain functions. these , g. have 2 for loops in code print these functions , set numberformat of columns general run automatically. these both work great.

the problem is, need save values in column f array before column deleted. if check box unselected (after being selected once- can't undo something has never been done @ least once) want print values array1 (the variable names work in progress) column f.

that part isn't working. code doesn't give syntax errors, logically incorrect , stumped. array1 seems run every time check-box clicked either on or off, meaning fills blank values since column f has been deleted.

here entirety of code far, trouble areas think issue have ////// comments above mark them:

private sub checkbox1_click() dim array1() string 'creates dynamic array dim temp worksheet dim alias_adds worksheet dim lastrow long  set alias_adds = sheets("alias_adds")  'find last row in sheet sheets("alias_adds")     lastrow = .range("b" & .rows.count).end(xlup).row end  'declares length of array1 value of lastrow redim array1(1 lastrow) string  'conditional statements start if checkbox1.value = true  'stores values cells in column f //////////this issue :( = 1 lastrow array1(i) = alias_adds.cells(i, "f") next  alias_adds         alias_adds.range("a:a,f:f,g:g").delete         'alias_adds.columns("e:f").entirecolumn.delete 'set e:f because after deleted in above line, columns shift left     end  end if 'end checkbox1.value = true boolean statement  if checkbox1.value = false         'insert new column left of current column in alias_adds sheet         alias_adds.columns("a:a").insert shift:=xltoright, copyorigin:=xlformatfromleftorabove          'for loop print validation function in newly inserted column         alias_adds.columns("a").numberformat = "general" 'set numberformat of column general before loop print functions         j = 1 lastrow         if (j > 1)         alias_adds.cells(j, "a") = "=b" & j & "&c" & j & "&d" & j         else: alias_adds.cells(j, "a") = "concatenate function"         end if         next j          '////////////////////         'for loop print stored values array1 cells in column f         k = 1 lastrow         alias_adds.cells(k, "f") = array1(k)         next k          'for loop print validation function in column g         alias_adds.columns("g").numberformat = "general" 'set numberformat of column general before loop print functions         l = 1 lastrow         if (l > 1)         alias_adds.cells(l, "g") = "=countif(a:a,a" & l & ")"         else: alias_adds.cells(l, "g") = "duplicate check?"         end if         next l   end if 'end checkbox1.value = false boolean statement  end sub 

also here before , after pictures:

before:

after: (column f empty, need filled same values previous pic)

thanks commented suggestions. ended creating work-around. since vba code run every time button checked or unchecked, unable find way array store info while code wasn't running.

my work around create separate sheet named "temp" , use array copy contents of column f sheet, when box unchecked, for loop takes data , places newly created column f in alias_adds sheet.

note: tried several times unable implement cor_blimey's suggestion. kept receiving error.

private sub checkbox1_click()     dim array1() string 'creates dynamic array     dim temp worksheet     dim alias_adds worksheet     dim lastrow long      set alias_adds = sheets("alias_adds")     set temp = sheets("temp")      'find last row in sheet     sheets("alias_adds")         lastrow = .range("b" & .rows.count).end(xlup).row     end      'declares length of array1 value of lastrow     redim preserve array1(1 lastrow) string      'conditional statements start     if checkbox1.value = true      'stores values cells in column f within array1     = 1 lastrow     array1(i) = alias_adds.cells(i, "f")     next      'prints contents of array1 temp sheet     temp.columns("f").numberformat = "@"     m = 1 lastrow     temp.cells(m, "f") = array1(m)     next m       alias_adds             alias_adds.range("a:a,f:f,g:g").delete             'alias_adds.columns("e:f").entirecolumn.delete 'set e:f because after deleted in above line, columns shift left         end      end if 'end checkbox1.value = true boolean statement      if checkbox1.value = false             'insert new column left of current column in alias_adds sheet             alias_adds.columns("a:a").insert shift:=xltoright, copyorigin:=xlformatfromleftorabove              'for loop print validation function in newly inserted column             alias_adds.range("a:a,f:f,g:g").interior.color = rgb(192, 192, 192)             alias_adds.columns("a").numberformat = "general" 'set numberformat of column general before loop print functions             j = 1 lastrow             if (j > 1)             alias_adds.cells(j, "a") = "=b" & j & "&c" & j & "&d" & j             else: alias_adds.cells(j, "a") = "concatenate function"             end if             next j              'for loop print stored values temp sheet cells in column f             k = 1 lastrow             alias_adds.cells(k, "f") = temp.cells(k, "f")             next k              'for loop print validation function in column g             alias_adds.columns("g").numberformat = "general" 'set numberformat of column general before loop print functions             l = 1 lastrow             if (l > 1)             alias_adds.cells(l, "g") = "=countif(a:a,a" & l & ")"             else: alias_adds.cells(l, "g") = "duplicate check?"             end if             next l       end if 'end checkbox1.value = false boolean statement      end sub