worksheet code

Hi,
Is it possible to add one more cell in the below work sheet code
("$A$10").



Private Sub Worksheet_Change(ByVal Target As Range)
Dim iPos As Long

On Error GoTo ws_exit:
Application.EnableEvents = False
With Target
If .Address = "$B$8" Then
If Len(.Value) > 50 Then
iPos = InStrRev(.Value, " ", 51)
If iPos > 0 Then
Offset(1, 0).Value = Right(.Value, Len(.Value) - iPos)
Value = Left(.Value, iPos)
End If
End If
End If
End With

ws_exit:
Application.EnableEvents = True
End Sub


thanks and regards
nowfal


-- 
nowfal
------------------------------------------------------------------------
nowfal's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=10003
View this thread: http://www.excelforum.com/showthread.php?threadid=397325

0
8/19/2005 6:39:32 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
548 Views

Similar Articles

[PageSpeed] 40

One way:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim iPos As Long

On Error GoTo ws_exit:
Application.EnableEvents = False
With Target
    If Intersect(.Cells, Me.Range("b8,a10")) Is Nothing Then
        Exit Sub
    End If
    If .Cells.Count > 1 Then
        Exit Sub
    End If
    If Len(.Value) > 50 Then
        iPos = InStrRev(.Value, " ", 51)
        If iPos > 0 Then
            .Offset(1, 0).Value = Right(.Value, Len(.Value) - iPos)
            .Value = Left(.Value, iPos)
        End If
    End If

End With

ws_exit:
Application.EnableEvents = True
End Sub

nowfal wrote:
> 
> Hi,
> Is it possible to add one more cell in the below work sheet code
> ("$A$10").
> 
> Private Sub Worksheet_Change(ByVal Target As Range)
> Dim iPos As Long
> 
> On Error GoTo ws_exit:
> Application.EnableEvents = False
> With Target
> If .Address = "$B$8" Then
> If Len(.Value) > 50 Then
> iPos = InStrRev(.Value, " ", 51)
> If iPos > 0 Then
> Offset(1, 0).Value = Right(.Value, Len(.Value) - iPos)
> Value = Left(.Value, iPos)
> End If
> End If
> End If
> End With
> 
> ws_exit:
> Application.EnableEvents = True
> End Sub
> 
> thanks and regards
> nowfal
> 
> --
> nowfal
> ------------------------------------------------------------------------
> nowfal's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=10003
> View this thread: http://www.excelforum.com/showthread.php?threadid=397325

-- 

Dave Peterson
0
petersod (12004)
8/19/2005 7:25:43 PM
Reply:

Similar Artilces:

scanning bar codes in pos
Is it possible to scan a bar code without having to press the enter key to make a transaction? It seems counter-productive to do it this way Frank, You should be able to program your scanner to add an 'ENTER' as a suffix to the scan. That is the way we do it. Marc "Frank" <nary@ec.rr.com> wrote in message news:1153175013.344333.97120@b28g2000cwb.googlegroups.com... > Is it possible to scan a bar code without having to press the enter key > to make a transaction? It seems counter-productive to do it this way > Some scanners require programming to s...

Trying to reference a workbook worksheet from a cell content
It is hard to explain what I am trying to do in the subject line: I have two workbooks: One is "Building Methods" The other is "Costs" I have a formulas in the Building Methods workbook that needs to look up a value in the Costs workbook, but the particular sheet it needs to look up the costs from has to be gotten from a cell in the Building Methods worksheet. For instance here is the formula that works: =IF(G14="ax",'[Costs.xls]2278'!$D$14,"") The problem is that I need the "2278" to be picked up form cell G4 rather than be specif...

Help correcting HTTP redirect code for OWA Exchange 2K3
Hi, I am using the code from Q article 55126 to redirect incomming http request to https for OWA. I keep getting the error below. Can someone help me with the solution to this. Thanks in advance --mdanny ****** Microsoft VBScript compilation error '800a0401' Expected end of statement /owa_redirect/owahttps.asp, line 1 If Request.ServerVariables("SERVER_PORT")=80 Then Dim strSecureURL strSecureURL = "https://" strSecureURL = strSecureURL & Request.ServerVariables("SERVER_NAME") strSecureURL = strSecureUR...

Getting Excel to return Hiragana using code/char functions
I have a Japanese computer which does this perfectly, but when I tried to use the worksheet on an English computer with Japanese language enabled, it only came up with errors. The code it was returning for the Japanese characters was much to low & putting in higher codes meant it didn`t recognise it. What can I do to make it work? ...

Summing up data from various worksheets
I have daily data all on different worksheets (31 in total) and I would like to add up all of the data in these worksheets in a monthly compilation I have so far been using the =sum('jan. 1'!B6, 'jan. 2'!B6 ... etc.) which although is effective is very time consuming is there an easier way to sum up all of the b6 cells without entering them individually? Thanks -- mark_vi_ ------------------------------------------------------------------------ mark_vi_'s Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24507 View this thread: http://www.excelf...

Parse XML into Collection
I'm a newbie so please bear with me... I'm looking to parse XML into a Collection so that I can pass that collection around to functions to extract data from it easily. I'm hoping there is code already to parse std XML files into a Collection? All I've found is code where the XML structure is already known... I need to code to work no matter what the structure... it would "figure out" what nodes are parents/children and such then place them into a Collection appropriately. Any help is appreciated! An XmlDocument already defines a hierarchy of collections when it p...

Formulas with a variable # of worksheets
I am building a workbook that is intended to capture information on new feature requests for a system. The workbook will then consolidate information from each new feature request and perform different kinds of analysis to facilitate portfolio management (ranking views of each feature to make a determination as to which features will be funded for the next release). Analysis (and input for each feature) will include things like value to the customer, value to the company, development cost, marketing risk, development risk, deployment/support risk, company strategy supported, etc. I think I ...

Filter every nth record within a worksheet...
Hi, In the past, I have used the Data/Filter/Advance Filter tool to get unique records based on text in a column. Is there a simple way to obtain the nth rows in a worksheet? For example, I wish to display the 4th, 8th, 12th, 16th, etc. rows. Or perhaps the 3rd, 6th, 9th, 12th, etc rows only. I thought if I could specify a criteria, this might work but I don't know how to approach this task. Thanks in advance for any help you can provide. -Greg I use a helper column and Data|filter|autofilter. I use a formula like: =mod(row(),4) Or =mod(row(),3) Then filter to show the 0's, 1'...

Activation Code for Money 2004 Deluxe
Can anyone get this link to work with either button. I keep getting the page can not be displayed. http://shop.microsoft.com/MoneyUpgrade/PurchaseQuestion.as p?SKU=860- 00211&SKUName=Mohttp://shop.microsoft.com/MoneyUpgrade/Pur chaseQuestion.asp?SKU=860-00211&SKUName=Money%20Deluxe% 202003%20English%20Upgrade%20From%20Money% 20Trial&img=/OMLibrary/images/X0880521frs.jpg Jason, Just use the link without the SKU: http://shop.microsoft.com/MoneyUpgrade/PurchaseQuestion.asp Good Luck! Regards, JB "jasona@arnell-west.com" <anonymous@discussions.microsoft.com>...

VBA codes question
Hi, I just write down some VBA codes for an application. What I need is extract some data from a selected area by mouse left-button. So what should I do ? The most I concerned is how to get the Range object of the shadow area selected by mouse. Thanks. ...

Keyboard Shortcut for moving between Worksheets
Is there a keyboard shortcut for moving between worksheets (within the same workbook)? I am constantly going between my keyboard and mouse for this functions.... Thanks, Mike Mike, ctrl+ page up and page down -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "mjcoco" <mjcoco@discussions.microsoft.com> wrote in message news:718956B2-44CD-4578-B95E-91C162D18A99@microsoft.com... > Is there a keyboard shortcut for mov...

Hide worksheets if macros are disable
Hi, what I was hoping to do is if the marcros are disable when the file is opened all the worksheets are hidden except one with a message saying to enable macros in which case this sheet would be hidden if the macros were enable. TIA If macros are disabled, there's nothing you can do to change they way it opens. Maybe you can save the file with the worksheets hidden the way you want. Then have your auto_open/workbook_open unhide the ones you want to see. Eintsein_mc2 wrote: > > Hi, what I was hoping to do is if the marcros are disable when the file > is opened all the work...

error code 0x80040154 my question is this, why doesn't MS take th
error code 0x80040154 my question is this, why doesn't MS take this error seriously? I came here for the first time because of this error. I was able to work around it ONLY because we had also purchased Office Pro. 2000 and can access the Clip Art from that disk. If anyone doesn't have this opportunity they are not going to be able to access their Clip Art in 2003 because of this error. I have come back and for two years individuals are still asking for help, a fix and a repair. NOTHING suggested ever helps or repairs this error and we have tried them all. I have the option of ...

holding or locking a worksheet
Let's say I have a file that has 20 worksheets on it. The first tab on the right is a summary tab. Is there anyway to make it so that the summary tab never moves. Say if I scroll to the 20th tab, the summary tab will always be the first tab on the left, instead of say tab 13. Right now I have links on every tab going back to the summary tab, but was just curious if this was possible. ...

How void receipt/shipment with wrong Item account codes
Series : Purchase Order Processing http://img341.imageshack.us/img341/135/20100312185625.png No sure by create a Purchase Return can help =96 my concern is wrong account codes & the standard cost. Under Item maintenance, the item (V0910) has already with wrong account codes and wrong standard cost, Has been wrong even BEFOFE the creation of the receipt shipment. So, the Shipment transaction is now with the wrong accounts. I am wondering if by doing a purchase return (Transaction > Purchasing > Return Transaction Entry) can help to reversing all the wrong entries f...

Code completion not working in VC++ 6.0
Hi, Anyone seen this before? Code completion is not working properly in visual C++ 6.0. When I type . or ->, I no longer see the member list after it. Once I type in a function and ( then it will show me the parameters. But not the member list. I checked and I do have all the code completion options selected. What gives? Not sure when this stopped working, I haven't used it in quite some time (probably a couple years!) but now am using it again and this is driving me nuts. Am thinking maybe I should reinstall but would like to avoid that (especially since who knows if it will e...

Saving without VBA code
I have a workbook that has some VBA code in the ThisWorkbook module to define a toolbar button. This button uses VBA code in another module to save a set of ranges to a new spreadsheet file. I accomplish this by creating a new workbook, copying each range (each is on a separate worksheet), then saving the new workbook with a new name. Unfortunately, the VBA code in ThisWorkbook is also copied to the new workbook, so when the new spreadsheet file is opened, it also has the toolbar button. Is there any way to do this without also copying the VBA code from ThisWorkbook? Or, is there a way to remo...

Hided values appears when I'm copying/pasting it to other worksheet
So, in one worksheet I have some data and there is some colums hided. When I selecting all that data and pasting to other worksheet, al these hided colums or rows values appears and I have to hide it again. Is there possibility to copy/paste in such way, that these hided value don't appear again? Thanks in advance Best Regards, Aivara -- Aivaras_Bakana ----------------------------------------------------------------------- Aivaras_Bakanas's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2518 View this thread: http://www.excelforum.com/showthread.php?threa...

Please help with code! Thanks!
I have a workbook of about 26 worksheets. Since it=92s pretty big, I disabled the save & saveas so the user can only save the current sheet (the current sheet is copied to another workbook) and the user is given an option to saveas (saveas dialog) pops up. The Private Sub Workbook is in the Workbook while the Sub SavingFile is in the module. The code works well because most of the time, there is only one visible sheet, so the code copies the one sheet to another workbook for saving. The problem is that sometimes, the visible sheets are more than one sheet. In cases where there are more t...

help with coding
Hi All, I have been doing some search on this topic and require some coding help. I want to create a login form which checks for username, pwd and securitylevel and accordingly give access to certain queries , forms and reports. I do not want to use the Access user level security wizard and want to create a separate security module. I have come across this code posted by Klatuu which is probably the solution i am looking for. Public Function SetSecurityProp(UserInitials As String, SecurityLevel As Integer) As Boolean Dim prp As Property Const conPropNotFound As Integer = 3270 ...

Can I use a worksheet name in a formula?
I'm using Office 2007 I have a workbook with 20+ pages. On one of them I want to use a formula that refers to a worksheet name, but in different cells I want it to refer to different worksheets. I have a cell that currently has a name typed into it that matches a worksheet name. Can I refer to the contents of that cell (INDIRECT) and use the content to refer to the correct worksheet and then go to that worksheet for the data? eg. Sheet called Christmas. In cell H2 value = Pudding on another sheet cell B2 value = Christmas. I want the formula in cell J12 to go to Sheet C...

Help with code --> Invlaid use of Null????
Hi all: Can someone please let me know what I am doing wrong in the below code.....it is not all the code but just part of it. What I am doing is using a form to email copies of a report to our growers/customers. When the code loops through our grower/customer table and the grower has an email address it works perfect. However, when it hits a grower without an email address it then fails and gives me an "invalid use of null" error. I thought I was checking for "null" email addresses and the processing below what code I have shown will handle them. The pr...

Embedded excel worksheets in word
Since Office v. X I have been unable to embed excel worksheets in word that contain more than five columns. Can anyone tell me that I have been doing something wrong OR tell me if 2004 fixes this problem? Thanks, Mike in article BCD52774.75C7%mcgetrick@lfoconnell.com, Mike McGetrick at mcgetrick@lfoconnell.com wrote on 22/05/04 21:51: > Since Office v. X I have been unable to embed excel worksheets in word that > contain more than five columns. Can anyone tell me that I have been doing > something wrong OR tell me if 2004 fixes this problem? > > Thanks, > Mike > A...

Unprotecting multiple worksheets w/ Macro
I am protecting with a macro, multiple worksheets in a workbook with a macro. I can't get the unprotect of multiple worksheets to work. Does anyone have a working macro to do this. _____________ Tippy Sub UnprotectSheets() Dim sh as Worksheets for each sh in ThisWorkbook.Worksheets if sh.ProtectContents or sh.ProtectScenarios or _ sh.ProtectDrawingObjects then sh.unprotect Password:="ABCD" end if Next End Sub -- Regards, Tom Ogilvy <tippy@att.net> wrote in message news:c7vlpv0sqb9eov641aj31ohnqnps7hvgvm@4ax.com... > I am protecting with a macro,...

X.400 Service error code 290
HI there, I require some assistance. We are running about a 20 user network and using Win NT SBS running Exchange 5.5 Thre is also a Linux Firewall setup which forwards incoming mail to the NT server. When sending mail the NT server forwards it to the Linux Box and then it gets sent. Everyone can send and receive both External and Internal email except one user who can do everything except send external email. The mail immediately bounces back with a error message from the administrator. When checking event viewer in the application log the following error has occured: A non-delive...