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-Hidequoted 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 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-Hide quoted text -
>
> > - Show quoted text -- 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
