• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Modifying list making macro to tie comment cells to proper list name cell. No whole rows.

S

sdfjh87687

New Member
Hi coders

Macro makes a list of all sheets in a file in first sheet of file. By clicking on sheet name, it moves to picked sheet. When want go back to sheet list, press ctrl+q.
In every new opening of file, it opens at first sheet (sheet list).

All works, but I would like to find some good person to write a macro for:

I need write comments in showed cells near list cells. Problem occur, when I change order of sheets in file. Comments stay at same position. I need move comment cells along with list cells.
Not whole rows, because I don't know what may I add next in future.

RjwwJXa.png


I always want make, edit, delete comments in first sheet(Sheet List). But it seems, comments has to be stored somewhere else and called back from that place.
What about to store them in each sheet macros ? Somebody told me to make some Public function(in each sheet ?), which gives back that text into first sheet.
But it would be ineffective to make manually macro for each sheet. Is possible to make overall macro to make automatically other smaller macros ? Also when I add new sheet, that overall macro, would make into new sheet that particular one.

Would be anyone so kind to code it ? This is my last chance. Here previous attempts:
http://www.ozgrid.com/forum/showthread.php?t=195514&p=750198
http://www.mrexcel.com/forum/excel-...ells-proper-list-name-cell-no-whole-rows.html
http://www.excelforum.com/excel-pro...s-to-proper-list-name-cell-no-whole-rows.html

Thanks for any help

main macro + back with macro:
Code: 
Sub Sheet_lister()
Dim ceLL As Range
Dim i As Long
Dim Button1 As Object
Dim Button2 As Object
Dim Ws1 As Worksheet
Dim Ws2 As Worksheet
Dim Exist As Boolean

Application.ScreenUpdating = False

For Each Ws1 In Worksheets
  If Ws1.Name Like "Sheet List" Then Exist = True: Exit For
  Next
  If Exist = True Then
  With Sheets("Sheet List")
  .Activate
  .Columns(1).Clear
  End With
  Else
  Sheets.Add before:=Worksheets(1)
  ActiveSheet.Name = "Sheet List"
   
  With Range("A1")
  Set Button2 = ActiveSheet.Buttons.Add(Left:=.Left, Top:=.Top, Width:=.Width, Height:=.Height)
  End With
   
  With Button2
  .Name = "Button2"
  .OnAction = "Sheet_lister"
  .Characters.Text = "Make list"
  .Characters.Font.Name = "Arial"
  .Characters.Font.Size = 11
  .Characters.Font.Color = vbRed
  '.Characters.Font.Bold = True
  End With
   
  End If


For i = 2 To Sheets.Count
Cells(i + 1, 1) = Sheets(i).Name
Columns(1).AutoFit

If Columns(1).ColumnWidth < 18 Then
  Columns(1).ColumnWidth = 18
End If

Cells(i + 1, 1).NumberFormat = "@* "
Next i


For Each ceLL In Range("A3", Range("A3").End(xlDown))
ceLL.Hyperlinks.Add Anchor:=ceLL, Address:="", _
SubAddress:="'" & Replace(ceLL.Value, "'", "''") & "'" & "!a1", ScreenTip:="Click to move to sheet", TextToDisplay:=ceLL.Value
Next

Range("A3:A" & Cells(Rows.Count, "A").End(xlUp).Row).Font.Underline = xlUnderlineStyleNone


Application.ScreenUpdating = True
Sheets("Sheet List").Activate
End Sub

Sub Back()
Sheets("Sheet List").Activate
End Sub


macro for first sheet display on start:
Code: 
Private Sub Workbook_Open()
Sheets("Sheet List").Activate
End Sub
 
Marc L said:
Hi !

When you change order of sheets, change order of comments as well ‼
​
Click to expand...

I know ! That's problem I need to code. I need a macro to fix comment cell positions with list cells positions. I wrote it.
Plus second big problem is, I need to modify comments only in list. Not in places, where it is stored and called from.
I think I explained all correctly.
 

How do you change order ?

With a sort, whatever in Excel or by code,
just use the global range of worksheets names and comments …
​
 
No no
I manually change order of sheets in file(according to my current needs. Delete some, make new sheets, move...). Then I press Make list button, it must make same order as in real. Plus tie comment cells with proper list cell during list making.
 
Marc L said:

So what's your analysis to tie comment cells ?
​
Click to expand...

Uhm I can't do anything with it, because I'm not programmer and don't know VBA.
I just brought all informations I could gather from others who helped me in the past.
I'm afraid, now I can only wait for someone, who may code it.

Problem set, solution is known, now just left some hardcoding.
 

No needs to be a programmer to analyse, just a bit of brain !
And a VBA beginner can perform this, nothing difficult …

For example, copy source data to the right before to clear source contents,
make a new list an then do a VLookup to search old comments,
just keep values and clear copied range. Just try with Macro recorder …

A VBA expert may use an array variable and Match Excel function …
​
 
Marc L said:
No needs to be a programmer to analyse, just a bit of brain !
And a VBA beginner can perform this, nothing difficult …

For example, copy source data to the right before to clear source contents,
make a new list an then do a VLookup to search old comments,
just keep values and clear copied range. Just try with Macro recorder …

A VBA expert may use an array variable and Match Excel function …
​
Click to expand...

Not sure if my english is bad or you used strage sentence, but I didn't get this:
"For example, copy source data to the right before to clear source contents,"

However, I don't want make any ancillary things in file (like helping sheet), which will mess my order of sheets and discract me from real sheets. I would have to be always keeping in mind, that there is something which has no real data for me.

Or if misunderstood, pls try describe solution beginner friendlier
Thanks
 
What I did:
I made ancillary cells for original comments in other column mirrored. I put this formula into each: =C5. And down.. =C6, =C7, ....

Then I wanted try VLOOKUP, but it said cycled formula.
Ancillary column has to be mirrored, because I wil be changing those comments
 
I don't get it into details. You say for example: make new list. Which one ? Ancillary to main ? Or make list by clicking Make list button ?

Please if you know how to do it, just paste, don't tease it. I wasted a lot of time to it.
 

The purpose remains in saving comments to proper sheets or not ?
So, where belongs difficulty in just copy / paste cells to another place in same worksheet ?! A 10 years schoolchildren can do that and
achieve all the procedure in a couple of minutes !
(I trained a class of schoolchildren a couple of hours
and they have succeeded far more complicated themselves !)
So train by doing actions manually until the end
and just by using Macro recorder you'll get a code base !
After you success, you'll post the generated code here
and we will see how to include to former one or in a new one.

How did you make the not too bad code (post #1) ?

The question is why face to a basic need and so easy to resolve
there was any answer from great others forums ? What did you done there ?
​
 
1, I didn't make that code, someone did it for me.
2, I know just basics. Excel basics. Not VBA. In VBA I know nothing.
3, I'm not sure you understand what I need. Did you try that macro or anything how it works ?
If not, I have to tell it all over again. Since you are only one willing to help me, I will to it. Read carefully please.

Imagine ordinary excel file with 30 sheets in it. No macro, nothing.
It's hard open every time other sheet searching down there and also remember what each one contains, because excel limits sheet's name length, so you can't describe everything in sheet name.
Therefore it came in my mind a solution to make a list of sheets in first sheet of file. Everytime I will open excel file, will be displayed list of sheets. When I click on sheet's name, it will move me to that sheet.
But what if I change manually order of those 30 sheets in file ? Or delete one, or add one sheet ? Therefore must be macro running more often. Ideally with button trigger. Each time I press, list will be updated. Ok.
But I still need to solve how remember what sheet contains. We have to have short names of sheets(we can't do anything with it). So I may write manually little comments almost next to list of sheets. Ok. But it must stay at it's place when list of sheets will change order. That is problem yes.
Everybody suggest to store same comments into second place, from which it will be extracted and put into proper place during changing list order.
Ok, we have several options to pick that place to store. A regular cells somewhere else in file. I don't like it and don't want this solution, because I don't know future changes I will do with sheets, so it must be safe enough, to not being touched even accidentally. Other option we have, is to store comments in macro itself in VBA window Alt+F11.
I need some good soul, who will code it for me please.

So in a nutshell, what should finished macro do:
- when I press button Make list, first thing it does, will be copy all comments into place somewhere in VBA
- then it will update order of sheets into list
- then it will paste comments from stored place, back almost next to sheet list. each comment to a proper list cell
- if I delete sheet, comment will be also deleted, if I add sheet, comments will be blank cell of course
 
sdfjh87687 said:
I know just basics. Excel basics. Not VBA.
Click to expand...
Well, better Excel codes are those who use Excel basics functions !
It's all I requested to you, it's only Excel basics …
And if you can't achieve manually, you can't then automate it !

So fisrt you need to save data, leave at list a blank column (D) …
​
 
Ok so you say (I don't know whether you are going to follow my needs or make your own solution, but I will do what you say).

Step 1: Keep column D empty. Ok np, it alredy is/was.
Step 2: ?
 
Jesus ! You tell me first: let column empty., Then: let copy it to it. I'm not into childish games. I'm really trying to solve something important to me. Obviously it's not for you, so...
I'm over. Bye
 

Try to well read 'cause I never wrote copy to it ‼

Where is the blank column between column C & D ?‼

emoticon-msn-rigole.gif

​
 
You must log in or register to reply here.
Back
Top