Self writing vba

[jasonb75]

Hi all

This is a follow on to http://www.mrexcel.com/forum/showthread.php?t=369569

The original problem has now been solved and the macro is fully working, but it has a limitation that I want to try and remove.

To do this I need to do something which I don't think will be possible, but I'm sure somebody will prove me wrong.

The macro is dynamic, based on an offset from the active cell when the macro is initialized.

This means it's not practical to define a variable for each possible cell that it could run from, but that's what I need.

Ideal solution is for the marco to write its own code
Directly after Sub mysub()

Static oldrow1 as integer

and directly before End Sub

oldrow1 = 1

This would work on the basis of 1 if the macro runs in row 1

if it runs in row 2 then it would be oldrow2 = 2 etc

Any ideas or possible solutions, or have I finally beaten everyone?

Thanks

Jason

 

[stanleydgromjr]

jasonb75,

From your posted link:

For anyone interested, I now have code that simulates excel 07's group function, with the expand / collapse link on the visible cell above the hidden range.

What version of Excel are you using?

Please post your macro code.

At the beginning of your posted code, enter the following without the quote marks:
["code"]

'Your code goes here.

At the end of your posted code, enter the following without the quote marks:
["/code"]


Have a great day,
Stan

 

[Peter_SSs]

Jason

I'm not sure I fully understand this, but it looks like a public variable (which was discussed in your other thread) would do what you want here.

Do either of these do the sort of thing you want?

<font face=Courier New><SPAN style="color:#00007F">Public</SPAN> oldrow1 <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br><br><SPAN style="color:#00007F">Sub</SPAN> mysub1()<br><br>    <SPAN style="color:#007F00">' other code here</SPAN><br>    <br>    oldrow1 = oldrow1 + 1<br>    MsgBox "No of times macro has been run = " & oldrow1<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>



<font face=Courier New><SPAN style="color:#00007F">Public</SPAN> oldrow1 <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br><br><SPAN style="color:#00007F">Sub</SPAN> mysub2()<br>    <SPAN style="color:#00007F">Dim</SPAN> myrow <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    myrow = ActiveCell.Row<br>    <SPAN style="color:#00007F">If</SPAN> oldrow1 = 0 <SPAN style="color:#00007F">Then</SPAN><br>        MsgBox "This is first run through macro"<br>    <SPAN style="color:#00007F">Else</SPAN><br>        MsgBox "Row used last time macro was run = " & oldrow1<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><br>    <SPAN style="color:#007F00">' other code here</SPAN><br>    <br>    oldrow1 = myrow<br>    MsgBox "Row used this time = " & oldrow1<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[jasonb75]

Hi guys, thanks for looking at this for me, problem now solved using a different method, I was overcomplicating things, a more logical approach worked easily.

Peter, not quite what I had in mind, don't think I explained too well.
Not had chance to try HTML codemaker yet, been on this code all day, going about it the wrong way.

Stanley, code below if you're interested, I'm using 07 but wanted the group function to use in a workbook which would need to be 03 compatible for other users, hence the reason for this code.

Gave up on it a while ago, but did a similar, more simple version of this for another forum user yesterday which inspired me to have another go at it.

The original code allowed worked on the basis that only 1 range would be visible at any time, when a new range was unhidden the previous was rehidden.

I was trying to force the code to remember all unhidden ranges so that multiple ranges could be unhidden at any time.

This was the original code. (in the worksheet module).

Private Sub Worksheet_FollowHyperlink(ByVal target As Hyperlink)
   Static hasRun As Boolean
    Static oldcell As Integer
      Static oldrow As Integer
        If target.Range.Row = oldrow Then
     Rows(oldrow + 1 & ":" & oldrow + 11).EntireRow.Hidden = True
    oldrow = Cells.SpecialCells(xlCellTypeLastCell).Row
    Exit Sub
    ElseIf hasRun Then
         Rows(oldrow + 1 & ":" & oldrow + 11).EntireRow.Hidden = True
          Rows(target.Range.Row + 1 & ":" & target.Range.Row + 11).EntireRow.Hidden = False
    Else
         Rows(target.Range.Row + 1 & ":" & target.Range.Row + 11).EntireRow.Hidden = False
    End If
 hasRun = True
     oldrow = target.Range.Row
End Sub

The new logical code is. (also in worksheet module)

Private Sub Worksheet_FollowHyperlink(ByVal target As Hyperlink)
   If Rows(ActiveCell.Row + 1 & ":" & ActiveCell.Row + 11).EntireRow.Hidden = False Then
    Rows(ActiveCell.Row + 1 & ":" & ActiveCell.Row + 11).EntireRow.Hidden = True
   ElseIf Rows(ActiveCell.Row + 1 & ":" & ActiveCell.Row + 11).EntireRow.Hidden = True Then
    Rows(ActiveCell.Row + 1 & ":" & ActiveCell.Row + 11).EntireRow.Hidden = False
  End If
End Sub

If anyone is considering using this then the code to insert the links and hide ranges by default is. (in workbook module).

Private Sub Workbook_Open()
    Application.ScreenUpdating = False
' select sheet and start cell for ranges to group
     Range("sheet1!A2").Select
' define end of range, number of rows to offset each range and loop
For a = ActiveCell.Row To Cells.SpecialCells(xlCellTypeLastCell).Row Step 12
' select next range
    Range("A" & a).Select
' select offset rows to hide
     Rows(a + 1 & ":" & a + 11).EntireRow.Hidden = True
' create link in active cell to open / close group
' link based on cell content, blanks will not link
       ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
        "sheet1!A" & a, TextToDisplay:=ActiveCell.Text
Next a
   ActiveWorkbook.Save
End Sub

Note code is based on ranges of 1 row visible with link added to the visible populated cell (in col A), 11 rows hidden. Linking code starts row 2 assuming row 1 for headers.