Suppose you need to enter the following content. Since there is no connection function, you have to enter repeated characters repeatedly, which undoubtedly reduces work efficiency.
Customer address
Dongguan City, Guangdong Province Quanxing Co., Ltd.
Dongguan Hongyun Co., Ltd., Guangdong Province
Dongguan Zhongtian Co., Ltd., Guangdong Province
Dongguan City, Guangdong Province Yuemanlou Co., Ltd.
Xingyousheng Co., Ltd., Dongguan City, Guangdong Province
Solution ideas:
Use VBA to add a "Paste Special - Connect" function to WPS Form 2005, and place it in the right-click menu for easy calling.
Step 1. Create a macro for the "Paste Special - Connect" function
This example assumes that your WPS already has a VBA environment;
1. Create a new workbook;
2. Use the shortcut key ALT+F11 to open the VBE environment;
3. Click the menu [Insert] [Module] and enter the following code in the module:
Sub Paste Special__Connect() DimrngAsString,NewShtAsString,OldShtAsString,texts,iAsByte,ansAsByte IfActiveSheet.ProtectContentsThenMsgBox"The worksheet has been protected, this program refuses to execute!",64,"Prompt":ExitSub OnErrorGoToendd rng=Selection.Address OldSht=ActiveSheet.Name Application.DisplayAlerts=False Worksheets.Add NemSht=ActiveSheet.Name Range("a1").Select ActiveSheet.Paste IfActiveSheet.UsedRange.Count>1Then MsgBox"Can only connect the value of a single cell.",64,"andysky" GoToendd EndIf texts=Selection.Text Application.ScreenUpdating=True Sheets(OldSht).Select ans=InputBox("Please choose whether to connect before the target or after the target."&Chr(10)&"1: Connect before the target;"_ &Chr(10)&"2: Connect after the target.","Connection method",1) Fori=1ToSelection.Count If--ans=1ThenSelection(i)=textsSelection(i) If--ans=2ThenSelection(i)=Selection(i)texts Next endd: Worksheets(NemSht).Delete EndSub
Step 2. Add the macro function to the right-click menu
1. Use the shortcut key CTLR+R to open the "Project Explorer", find "ThisWorkbook" in the left pane and double-click it;
2. Enter the following code in the code window on the right (two pieces of code: the first is to add the tool function to the right-click menu, and the other is to restore the right-click when closing EXCEL):
PrivateSubWorkbook_Open() WithApplication.CommandBars("cell").Controls.Add(Type:=msoControlButton,before:=4,Temporary:=True) .Caption="Paste Special__Connection(&Paste)" .OnAction="Paste Special__Connection" EndWith EndSub PrivateSubWorkbook_BeforeClose(CancelAsBoolean) Application.CommandBars("CELL").Reset EndSub
Step 3. Save the workbook as default template
1. Close the VBE window and return to the worksheet;
2. Click the menu [File] [Save As] and select "WPS Table Template File" as the save type.
Step 4. Test the effect 1. Select the menu [File] [Template on this machine] and select the template you just saved in the list;
2. Enter the following data in the worksheet (see Figure 5), then copy A9, then select A2:A7, right-click and select "Paste Special__Connection" in the menu;
3. At this time, the program will pop up a dialog box, see Figure 7. Enter 1 in it to connect to the front. After clicking OK, the final result is shown in Figure 8.
Conclusion: ET supports VBA for secondary development, which makes it more flexible. When the default functions cannot meet our work needs, we might as well use VBA to handle it.
