Hi Paul,
Thanks aton....
It's working perfect....
And I done it for userform2... :)
Learned alot....
Thanks once again for your continuous help/guidance.... :) :) :)
Regds,
Manish
On Feb 7, 10:16�pm, Paul Schreiner <schreiner_p...@att.net> wrote:
> Manish,
>
> I'm sorry... I've been away from my computer(s) for a few days.
>
> I'll try to take a look today.
> I thought I sent it, but don't see it, so I'll have to re-create it.
>
> Paul
>
> ________________________________
> From: Manish <pansari.man...@gmail.com>
> To: MS EXCEL AND VBA MACROS <excel-macros@googlegroups.com>
> Sent: Mon, February 7, 2011 10:34:59 AM
> Subject: Re: $$Excel-Macros$$ User form value in current active row
>
> Any help pls.
>
> -
> Manish
>
> On Feb 7, 12:12 pm, Manish <pansari.man...@gmail.com> wrote:
>
>
>
> > Dear Paul & EE,
>
> > I want to write the value of userform in same PRA worksheet in column
> > T:AA in same row.
>
> > For example: if user enter the value is in PRA Sheet column M8,
> > userform will write the value in cell T8:AA8, same as if user enter
> > the value in PRA Sheet column M11, I want to write the userform value
> > in cell T11:AA11. Further if user change the previous entered value, I
> > want to show the same row value in userform textboxes like if user
> > change the value in cell M8, I want the user form open with the value
> > already I have in cell T8:AA8 and by clicking the OK button will
> > replace the value in cell T8:AA8. I hope my query/imagination is
> > clear.
>
> > Mr. Paul suggested me the following changes, but I don't know the way
> > to incorporate the same in my excel file.
>
> > Try this:
> > Declare a variable:
> > Public CellRange As Range
> > In the Change Event, set CellRange = to the cell that was changed:
>
> > Set CellRange = Target
>
> > This will make it available to the userform buttons.
> > Next.. Create a Userform Initialize macro:
>
> > Private Sub UserForm_Initialize()
> > UserForm1.TextBox1 = Cells(CellRange.Row, "T").Value
> > UserForm1.TextBox2 = Cells(CellRange.Row, "U").Value
> > UserForm1.TextBox3 = Cells(CellRange.Row, "V").Value
> > UserForm1.TextBox4 = Cells(CellRange.Row, "W").Value
> > UserForm1.TextBox5 = Cells(CellRange.Row, "X").Value
> > UserForm1.TextBox6 = Cells(CellRange.Row, "Y").Value
> > UserForm1.TextBox7 = CellRange.Value
> > UserForm1.TextBox16 = Cells(CellRange.Row, "AA").Value
> > End Sub
>
> > In the OK button:
>
> > Sheets("PRA").Cells(CellRange.Row, "T") = TextBox1.Value
> > Sheets("PRA").Cells(CellRange.Row, "U") = TextBox2.Value
> > Sheets("PRA").Cells(CellRange.Row, "V") = TextBox3.Value
> > Sheets("PRA").Cells(CellRange.Row, "W") = TextBox4.Value
> > Sheets("PRA").Cells(CellRange.Row, "X") = TextBox5.Value
> > Sheets("PRA").Cells(CellRange.Row, "Y") = TextBox6.Value
> > Sheets("PRA").Cells(CellRange.Row, "Z") = TextBox7.Value
> > Sheets("PRA").Cells(CellRange.Row, "AA") = TextBox16.Value
>
> > Can anyone please incorporate it in my excel file.
> > I am mailing my file on excel-macros@googlegroups.com
>
> > Regds,
> > Manish Pansari
>
> > On Feb 1, 7:26 pm, Manish <pansari.man...@gmail.com> wrote:
>
> > > Thank Paul,
> > > I don't have ops to attach file on group. Can I send you my file
> > > through mail on your personal mail id?
>
> > > -
> > > Manish
>
> > > On Jan 31, 7:57 pm, Paul Schreiner <schreiner_p...@att.net> wrote:
>
> > > > Was able to finish writing scripts to load all tables (1,384,800
> > > > records).
> > > > Now just have to wait to get a "fresh" data dump and run it complete.
> > > > It takes about 4 hours, because I have to use the Development server.
> > > > thanks for asking.
>
> > > > Now, about your issue.
>
> > > > you're "not comfortable when sheet is protected".
>
> > > > is the entire sheet locked/protected?
>
> > > > Do you have control over the protection? or is it owned by others?
>
> > > > how do you want it to behave?
>
> > > > can you send me what you have so far and let me look at it?
>
> > > > Paul
>
> > > > ________________________________
> > > > From: Manish <pansari.man...@gmail.com>
> > > > To: MS EXCEL AND VBA MACROS <excel-macros@googlegroups.com>
> > > > Sent: Mon, January 31, 2011 6:23:04 AM
> > > > Subject: Re: $$Excel-Macros$$ User formvalueincurrentactiverow
>
> > > > I tried the code suggested by you. It s working
> > > > Bt I am not comfortable when sheet it protected.
> > > > If possible, pls attach the sample file.
>
> > > > Thanks once again!!
>
> > > > What about your new development 600,000 records into an Oracle
> > > > database ?
> > > > I know, you are mastermind and you done it. :)
>
> > > > Regards,
> > > > Manish
>
> > > > On Jan 25, 6:38 pm, Paul Schreiner <schreiner_p...@att.net> wrote:
>
> > > > > I've got a macro running right now, so I cannot test the code, but I
> > > > > can
> > > > > describe it!
>
> > > > > Right-click on your sheet name "tab" and select "View Code".
> > > > > In the VBA editor panel, in the Top-left pull-down, select WorkSheet.
> > > > > The "default" event may be the Worksheet_Change event
> > > > > or the WorkSheetSelection_Change event.
> > > > > If it's the Change event, use the right-hand pull-down to select
> > > > > the SelectionChange event.
>
> > > > > This macro will "trigger" whenever you select a cell on the sheet.
>
> > > > > It passes the cell "range" as a variable called "Target".
>
> > > > > Now, the issue here is that if you select more than one cell,
> > > > > the Target variable is an array instead of a single range.
> > > > > So, I usually test it:
> > > > > if (Target.count = 1) then
>
> > > > > next, test to see if the column of the cell is column "B"
> > > > > If (Target.Column = 2) then
>
> > > > > You can save theRownumber by using:
> > > > > CurRow = Target.Row
>
> > > > > Of course, within youruserformmacros, you can use ActiveCell.Row
>
> > > > > Now, to launch youruserform, use: UserFormName.Show
>
> > > > > Give that a try.
> > > > > If you need more help, let me know and I'll write it when my macro
> >finishes.
> > > > > (It's loading 600,000 records into an Oracle database.. it takes a
> while)
>
> > > > > Paul
>
> > > > > ________________________________
> > > > > From: Manish <pansari.man...@gmail.com>
> > > > > To: MS EXCEL AND VBA MACROS <excel-macros@googlegroups.com>
> > > > > Sent: Tue, January 25, 2011 8:10:47 AM
> > > > > Subject: Re: $$Excel-Macros$$ User formvalueincurrentactiverow
>
> > > > > Thanks Paul.
> > > > > My column is selectable.
> > > > > If user select/edit in the cell e.g. B3, i want to open the user form
> > > > > and want to write user formvaluein samerowi.e. row3 in column C to
> > > > > H. And also i want to put total CtoH in column B.
>
> > > > > Thanks in advance.
>
> > > > > Regds,
> > > > > Manish
>
> > > > > On Jan 25, 5:54 pm, Paul Schreiner <schreiner_p...@att.net> wrote:
>
> > > > > > Is column B "selectable"?
>
> > > > > > (When setting protection, it is possible to protect the cell
> > > > > > from changes, yet allow the user to select the cell)
>
> > > > > > If it is, then you can use a Selection_Change event
> > > > > > and check:
> > > > > > If (Target.Column = 2) then
>
> > > > > > within the event, you can open theuserform.
>
> > > > > > You'll be able to make changes to other cells,
> > > > > > but to make changes to column B, you'll have to
> > > > > > know the password and unprotect the sheet.
>
> > > > > > Paul
>
> > > > > > ________________________________
> > > > > > From: Manish <pansari.man...@gmail.com>
> > > > > > To: MS EXCEL AND VBA MACROS <excel-macros@googlegroups.com>
> > > > > > Sent: Tue, January 25, 2011 1:37:03 AM
> > > > > > Subject: $$Excel-Macros$$ User formvalueincurrentactiverow
>
> > > > > > Dear Excel Experts,
>
> > > > > > Can you please suggest me the code for following task.
>
> > > > > > My sheet is password protected and Column B is locked. When user try
> > > > > > to enter the data in any cell of column B, I want to open one user
> > > > > > form.
> > > > > > I am comfortable with build the user form. In my user form I have
> > > > > > six
> > > > > > TaxtBox, I want to write the data of user form in same sheetactive
> > > > > >rowcolumn C to H.
>
> > > > > > I know the write the user form data in new worksheet, but I don’t
> > > > > > know
> > > > > > the code to write the data inactiverowand also the code for open
> > > > > > theUserform, if my cell is locked.
>
> > > > > > Pls help.
>
> > > > > > Thanks in Advance…
>
> > > > > > Regds,
> > > > > > Manish Pansari
> > > > > > +91 9810 88 8281
>
> > > > > > --
>
> >>>------------------------------------------------------------------------�--
> >--------
> > > > >-
> > > > > >-
>
> > > > > > Some important links for excel users:
> > > > > > 1. Follow us on TWITTER for tips tricks and links
> > > > > >:http://twitter.com/exceldailytip
> > > > > > 2. Join our LinkedIN group
> > > > > > @http://www.linkedin.com/groups?gid=1871310
> > > > > > 3. Excel tutorials athttp://www.excel-macros.blogspot.com
> > > > > > 4. Learn VBA Macros athttp://www.quickvba.blogspot.com
> > > > > > 5. Excel Tips and Tricks athttp://exceldailytip.blogspot.com
>
> > > > > > To post to this group, send email to excel-macros@googlegroups.com
>
> > > > > > <><><><><><><><><><><><><><><><><><><><><><>
> > > > > > Like our page on facebook , Just follow below
> > > > > >linkhttp://www.facebook.com/discussexcel
>
> > > > > --
>
> >>-------------------------------------------------------------------------�--
> >-------
> > > > >-
>
> > > > > Some important links for excel users:
> > > > > 1. Follow us on TWITTER for tips tricks and links
> > > > >:http://twitter.com/exceldailytip
> > > > > 2. Join our LinkedIN group @http://www.linkedin.com/groups?gid=1871310
> > > > > 3. Excel tutorials athttp://www.excel-macros.blogspot.com
> > > > > 4. Learn VBA Macros athttp://www.quickvba.blogspot.com
> > > > > 5. Excel Tips and Tricks athttp://exceldailytip.blogspot.com
>
> > > > > To post to this group, send email to excel-macros@googlegroups.com
>
> > > > > <><><><><><><><><><><><><><><><><><><><><><>
> > > > > Like our page on facebook , Just follow below
> > > > >linkhttp://www.facebook.com/discussexcel-Hidequotedtext-
>
> > > > > - Show quoted text -
>
> > > > --
>
> >--------------------------------------------------------------------------�--------
>
> > > > Some important links for excel users:
> > > > 1. Follow us on TWITTER for tips tricks and links
> >:http://twitter.com/exceldailytip
> > > > 2. Join our LinkedIN group @http://www.linkedin.com/groups?gid=1871310
> > > > 3. Excel tutorials
>
> ...
>
> read more �- Hide quoted text -
>
> - Show quoted text -
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
