Creating a "Sort" hyperlink in code

[jerH]

I have built a utility that reads in a .csv file, does a bunch of calculations, and spits out a formatted report. Within the report there are multiple "sections". The user has now asked if I can make the column headings "clickable" in order to sort a section in either ascending or descending order (toggle back and forth) by just clicking on the column heading. Any pointers on how to achieve this? I can provide as much detail as necessary

 

[jerH]

Maybe I spoke a bit too soon!

Actually the issue isn't really with the code you provided...it does work brilliantly. But when I sent the file to the end user I encountered a security issue.

The project is password protected so that no one else can mess around with the code. In my testing, I always had the project unlocked as I was editing away in the VBE. But for the end user, when you reach the part of the code that adds the event handler to the newly created sheet, you get an error: Run-time error '50289': Can't perform operation since the project is protected.

From what I've read, it's not possible to unprotect/protect a project through code...even if it was I assume that would involve putting the plain text password in your code, and if anything ever went wrong and the program crashed it may be accessible. But that seems to leave me with not being able to protect the project....

Any ideas? I know we're going a bit astray from the original question here.

 

[tonyyy]

Any ideas?

Don't password protect the VBAProject. Make clear to the user that if s/he messes up the code which then leads to inaccurate results/conclusions, that's on her/him.

 

[FormR]

Any ideas? I know we're going a bit astray from the original question here.

Hi, you could also consider copying an existing (hidden) blank sheet that already contains the event code and populating that rather than creating a brand new sheet and programmatically adding the event code.

 

[tonyyy]

Hi, you could also consider copying an existing (hidden) blank sheet that already contains the event code and populating that rather than creating a brand new sheet and programmatically adding the event code.

Wish I'd thought of that. Thanks for posting, FormR.

 

[jerH]

Brilliant! Thanks all....sent it off this morning, password protection still in place!

 

[jerH]

I'm back!
There is one hiccup with this solution that I have uncovered...I've been trying to come up with a way to fix it, but no luck....

Early on the thread I mentioned that there were issues with merged cells. You suggested that rather than merge cells, I use HorizontalAlignment = xlCenterAcrossSelection and that seemed to work...except.

Below is a screenshot of a portion of the report

Everything works great in the upper portion of the report (rows 30-36). But in the lower portion (rows 38-44) there's a problem. If we look at the column with the heading "CLOSING PWR", that is columns E and F of the worksheet. The text "CLOSING PWR" is the value in E38, and F38 is blank. The text is just centered across the two columns. Similarly, "XOTIC PWR" is columns G, H, and I. The text is in G while H and I are blank. All those blank columns wreck the CurrentRegion property. As far as Excel is concerned, when I double click on "XOTIC PWR" in column G, there is a blank column to the left of it (F) and to the right (H) and so it just sorts that single column and breaks the relationship with the rest of the data. I've tried putting blanks, or text in white font in the blank columns, but of course that wrecks the horizontal alignment. Any ingenious solutions?

 

[tonyyy]

In addition to posting a picture, it would be helpful if you posted your sample data.

 

[jerH]

How would I post a workbook to the forum? I'm assuming that's what you mean? I didn't think that was possible....
I could PM you a google drive link...the guy I am building this for sells the reports that this thing generates, so I don't want to share a link to the world....

Thanks for all your help!

 

[tonyyy]

No, I don't want the workbook nor a link to the workbook. Please follow the link in my signature on how to post Excel data.

 

[jerH]

Got it...thanks!


Excel 2016 (Windows) 32 bit

	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]A[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]B[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]C[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]D[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]E[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]F[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]G[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]H[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]I[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]J[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]K[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]L[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]M[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]N[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]O[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]P[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Q[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]R[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]S[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]24[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]25[/COLOR]​	AQU	Race 1		MdSpWt		Surface	D		Age 3yr only
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]26[/COLOR]​	Post Time (12:20)			Purse 60000		Distance 9 F			Sex NoSexRest
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]27[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]28[/COLOR]​								PACE PARS:​				90​	91​	89​	270​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]29[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]30[/COLOR]​	PRG#	AGE​	NAME	AE​	REC FIG	RS	ML​	MD​	AC​	~​	RC​	EP​	MP​	LP​	TP​	SR​	TR​	AR​	DLR​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]31[/COLOR]​	6​	3Y 4M​	HOLLAND PARK		76​	E6	0.8​	1​	72​	~​	72​	100​	105​	80​	285​	84​	78​	104​	39​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]32[/COLOR]​	1​	3Y 7M​	ROAMING UNION		63​	E/P4	4​	1​	75​	~​	71​	88​	91​	99​	278​	80​	94​	103​	34​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]33[/COLOR]​	3​	3Y 6M​	POWER BOSS		88​	P2	6​	1​	73​	~​	71​	89​	93​	91​	273​	78​	90​	101​	32​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]34[/COLOR]​	2​	3Y 5M​	HOARD		59​	P2	12​	1​	67​	~​	63​	85​	86​	91​	262​	76​	95​	97​	34​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]35[/COLOR]​	5​	3Y 4M​	LONELY WEEKEND		67​	S0	3​	1​	58​	~​	61​	77​	83​	97​	257​	82​	96​	96​	34​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]36[/COLOR]​	4​	3Y 6M​	BOURBON BOY		47​	S0	15​	1​	56​	~​	56​	67​	64​	77​	208​	67​	96​	81​	32​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]37[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]38[/COLOR]​				SPEED PWR​	CLOSING PWR​		XOTIC PWR​				LONE SPEED​			ALT SPEED​			CONFIDENCE​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]39[/COLOR]​	6​	3Y 4M​	HOLLAND PARK	99​	88​		+91​				+11.5​			10.3​			87​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]40[/COLOR]​	1​	3Y 7M​	ROAMING UNION	101​	107​		+93​				-13.0​			10.7​			83​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]41[/COLOR]​	3​	3Y 6M​	POWER BOSS	99​	101​		+91​				-11.5​			10.4​			76​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]42[/COLOR]​	2​	3Y 5M​	HOARD	100​	103​		+89​				-17.0​			10.3​			59​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]43[/COLOR]​	5​	3Y 4M​	LONELY WEEKEND	96​	107​		+88​				-22.5​			10.5​			45​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]44[/COLOR]​	4​	3Y 6M​	BOURBON BOY	91​	96​		+76​				-37.0​			9.3​			-6​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]45[/COLOR]​

<tbody>
</tbody>

Sheet: AQU_20180118

<tbody>
</tbody>