Modify Command Button
 

I have the following code attach to my command button:


Code
-------------------

Private Sub CommandButton1_Click()
If OptionButton1 = True Then
Range("cellname").EntireColumn.Insert
Worksheets("CGS").Cells(11, Range("cellname").Column - 1).Formula = TextBox1.Value
Worksheets("CGS").Cells(10, Range("cellname").Column - 1).Value = CDbl(Calendar1.Value)
Else
Range("cellname2").EntireColumn.Insert
Worksheets("CGS").Cells(11, Range("cellname2").Column - 1).Formula = TextBox1.Value
Worksheets("CGS").Cells(10, Range("cellname2").Column - 1).Value = CDbl(Calendar1.Value)
End If
Unload Me
End Sub

-------------------


This code allows me to create a column in front of my 'cellname' colum
and puts the text I add to 'textbox1' at the top of that column with th
date I enter on my calendar.

-Problem-
I have added some extra textboxex to my userform that will allow me t
enter text into specified cells in my worksheet using this code:


Code
-------------------

range("C10").value = textbox2.text

-------------------


I want to add this code to the code above and I want the range cel
range code to stay with the insert colum code.

So that evrytime I add a new column the text that I enter in t
'textbox2' will go to cell 'c10' in the new column that was jus
added.

Please help me with this code.

Thank you for your suppor

--
oberon.blac
-----------------------------------------------------------------------
oberon.black's Profile: http://www.excelforum.com/member.php...fo&userid=2673
View this thread: http://www.excelforum.com/showthread.php?threadid=46838

Modify Command Button
 

Still looking for some help on this one.


--
oberon.black
------------------------------------------------------------------------
oberon.black's Profile: http://www.excelforum.com/member.php...o&userid=26732
View this thread: http://www.excelforum.com/showthread...hreadid=468385

Modify Command Button
 

Still Looking for some help on this on

--
oberon.blac
-----------------------------------------------------------------------
oberon.black's Profile: http://www.excelforum.com/member.php...fo&userid=2673
View this thread: http://www.excelforum.com/showthread.php?threadid=46838

Modify Command Button
 

still aroun

--
oberon.blac
-----------------------------------------------------------------------
oberon.black's Profile: http://www.excelforum.com/member.php...fo&userid=2673
View this thread: http://www.excelforum.com/showthread.php?threadid=46838

Modify Command Button
 

still need help


--
oberon.black
------------------------------------------------------------------------
oberon.black's Profile: http://www.excelforum.com/member.php...o&userid=26732
View this thread: http://www.excelforum.com/showthread...hreadid=468385

Modify Command Button
 

Good afternoon oberon.black

Does this help your cause...?

Cells(1, ActiveCell.Column).Value = TextBox2.Text

HTH

Dominic

--
dominic
-----------------------------------------------------------------------
dominicb's Profile: http://www.excelforum.com/member.php...fo&userid=1893
View this thread: http://www.excelforum.com/showthread.php?threadid=46838

Modify Command Button
 

I expect the reason you have not had an answer is becouse it is not clear
what your problem is.

range("C10").value = textbox2.text

That should work just fine, eg

Private Sub TextBox2_Change()
Range("C10").Value = TextBox2.Text
End Sub

or

Private Sub TextBox2_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Range("C10").Value = TextBox2.Text
End Sub

Regards,
Peter T


"oberon.black"
wrote in message
news:oberon.black.1vh7yb_1126915529.4518@excelforu m-nospam.com...

I have the following code attach to my command button:


Code:
--------------------

Private Sub CommandButton1_Click()
If OptionButton1 = True Then
Range("cellname").EntireColumn.Insert
Worksheets("CGS").Cells(11, Range("cellname").Column - 1).Formula =
TextBox1.Value
Worksheets("CGS").Cells(10, Range("cellname").Column - 1).Value =
CDbl(Calendar1.Value)
Else
Range("cellname2").EntireColumn.Insert
Worksheets("CGS").Cells(11, Range("cellname2").Column - 1).Formula =
TextBox1.Value
Worksheets("CGS").Cells(10, Range("cellname2").Column - 1).Value =
CDbl(Calendar1.Value)
End If
Unload Me
End Sub

--------------------


This code allows me to create a column in front of my 'cellname' column
and puts the text I add to 'textbox1' at the top of that column with the
date I enter on my calendar.

-Problem-
I have added some extra textboxex to my userform that will allow me to
enter text into specified cells in my worksheet using this code:


Code:
--------------------

range("C10").value = textbox2.text

--------------------


I want to add this code to the code above and I want the range cell
range code to stay with the insert colum code.

So that evrytime I add a new column the text that I enter in to
'textbox2' will go to cell 'c10' in the new column that was just
added.

Please help me with this code.

Thank you for your support


--
oberon.black
------------------------------------------------------------------------
oberon.black's Profile:
http://www.excelforum.com/member.php...o&userid=26732
View this thread: http://www.excelforum.com/showthread...hreadid=468385

Modify Command Button
 

OK maybe I was not clear on what I am trying to do.


I want to add this code (A) :

***'x' is being used as a variable***


Code:
--------------------

range("x10").value = textbox2.text

--------------------


to this code (B):


Code:
--------------------

Private Sub CommandButton1_Click()
If OptionButton1 = True Then
Range("cellname").EntireColumn.Insert
Worksheets("CGS").Cells(11, Range("cellname").Column - 1).Formula = TextBox1.Value
Worksheets("CGS").Cells(10, Range("cellname").Column - 1).Value =
CDbl(Calendar1.Value)
Else
Range("cellname2").EntireColumn.Insert
Worksheets("CGS").Cells(11, Range("cellname2").Column - 1).Formula =
TextBox1.Value
Worksheets("CGS").Cells(10, Range("cellname2").Column - 1).Value =
CDbl(Calendar1.Value)
End If
Unload Me
End Sub

--------------------


So that the information that is entered into textbox2 will go into the
new column that is created by code (B). Therefore everytime I add a new
column using code (B) range(x) will be equal to textbox2 and be placed
in that new column.

Example

I run code (B) and it creates column 'D'. I want the info that I put in
textbox2 to be entered in 'D10'. Then if I run code (B) again and it
creates column 'E' I want the info that I enter into textbox2 to be
entered into 'E10' and so on. So that everytime I run code(B) the
information in textbox to will be entered in to the newcolumn and into
row 10 of that column.

I hope this is a clearer explanation of what I am trying to do.

Please help me develope this code.

Thank you for your support.


--
oberon.black
------------------------------------------------------------------------
oberon.black's Profile: http://www.excelforum.com/member.php...o&userid=26732
View this thread: http://www.excelforum.com/showthread...hreadid=468385

Modify Command Button
 

still need help resolving this issue


--
oberon.black
------------------------------------------------------------------------
oberon.black's Profile: http://www.excelforum.com/member.php...o&userid=26732
View this thread: http://www.excelforum.com/showthread...hreadid=468385

Modify Command Button
 

Is this what you are looking for -

Dim nCol as long

If OptionButton1 = True Then
Range("cellname").EntireColumn.Insert
nCol = Range("cellname").Column
'code
Else
Range("cellname2").EntireColumn.Insert
nCol = Range("cellname2").Column
'code
End if

Cells(10,nCol).value = textbox2.text

You might need to qualify Cells(10,nCol) with whatever worksheet ("CGS" ?)
if not the active sheet.

Regards,
Peter T

"oberon.black"
wrote in message
news:oberon.black.1viuab_1126991104.6034@excelforu m-nospam.com...

OK maybe I was not clear on what I am trying to do.


I want to add this code (A) :

***'x' is being used as a variable***


Code:
--------------------

range("x10").value = textbox2.text

--------------------


to this code (B):


Code:
--------------------

Private Sub CommandButton1_Click()
If OptionButton1 = True Then
Range("cellname").EntireColumn.Insert
Worksheets("CGS").Cells(11, Range("cellname").Column - 1).Formula =
TextBox1.Value
Worksheets("CGS").Cells(10, Range("cellname").Column - 1).Value =
CDbl(Calendar1.Value)
Else
Range("cellname2").EntireColumn.Insert
Worksheets("CGS").Cells(11, Range("cellname2").Column - 1).Formula =
TextBox1.Value
Worksheets("CGS").Cells(10, Range("cellname2").Column - 1).Value =
CDbl(Calendar1.Value)
End If
Unload Me
End Sub

--------------------


So that the information that is entered into textbox2 will go into the
new column that is created by code (B). Therefore everytime I add a new
column using code (B) range(x) will be equal to textbox2 and be placed
in that new column.

Example

I run code (B) and it creates column 'D'. I want the info that I put in
textbox2 to be entered in 'D10'. Then if I run code (B) again and it
creates column 'E' I want the info that I enter into textbox2 to be
entered into 'E10' and so on. So that everytime I run code(B) the
information in textbox to will be entered in to the newcolumn and into
row 10 of that column.

I hope this is a clearer explanation of what I am trying to do.

Please help me develope this code.

Thank you for your support.


--
oberon.black
------------------------------------------------------------------------
oberon.black's Profile:
http://www.excelforum.com/member.php...o&userid=26732
View this thread: http://www.excelforum.com/showthread...hreadid=468385

Modify Command Button
 

So Should the new code look like this:


Code
-------------------

Private Sub CommandButton1_Click()
Dim nCol as long

If OptionButton1 = True Then
Range("cellname").EntireColumn.Insert
Worksheets("CGS").Cells(11, Range("cellname").Column - 1).Formula =
TextBox1.Value
nCol = Range("cellname").Column
Worksheets("CGS").Cells(10, Range("cellname").Column - 1).Value =
CDbl(Calendar1.Value)
Else
Range("cellname2").EntireColumn.Insert
Worksheets("CGS").Cells(11, Range("cellname2").Column - 1).Formula =
TextBox1.Value
nCol = Range("cellname2").Column
Worksheets("CGS").Cells(10, Range("cellname2").Column - 1).Value =
CDbl(Calendar1.Value)
End If

Cells(10,nCol).value = textbox2.text

Unload Me
End Sub

-------------------


I have no idea how this code should be written.

Please help me get this code written up correctly.

Thank you for your suppor

--
oberon.blac
-----------------------------------------------------------------------
oberon.black's Profile: http://www.excelforum.com/member.php...fo&userid=2673
View this thread: http://www.excelforum.com/showthread.php?threadid=46838

Modify Command Button
 

still struggling with this


--
oberon.black
------------------------------------------------------------------------
oberon.black's Profile: http://www.excelforum.com/member.php...o&userid=26732
View this thread: http://www.excelforum.com/showthread...hreadid=468385

Modify Command Button
 

Based on my understanding of what you want are trying to achieve the code
might indeed look something like what you posted below.

My understanding was that you want to place textbox2.text in row 10 of the
column number of the named cell, which may be repositioned following a
column insert. The named cell being either cellname or cellname2 depending
on the If evaluation.

In the years I've been following this ng I don't think I've ever seen anyone
bump their own post 6 times in 24hrs. Must be some kind of record!

Regards,
Peter T

"oberon.black"
wrote in message
news:oberon.black.1vjdqc_1127016313.7222@excelforu m-nospam.com...

So Should the new code look like this:


Code:
--------------------

Private Sub CommandButton1_Click()
Dim nCol as long

If OptionButton1 = True Then
Range("cellname").EntireColumn.Insert
Worksheets("CGS").Cells(11, Range("cellname").Column - 1).Formula =
TextBox1.Value
nCol = Range("cellname").Column
Worksheets("CGS").Cells(10, Range("cellname").Column - 1).Value =
CDbl(Calendar1.Value)
Else
Range("cellname2").EntireColumn.Insert
Worksheets("CGS").Cells(11, Range("cellname2").Column - 1).Formula =
TextBox1.Value
nCol = Range("cellname2").Column
Worksheets("CGS").Cells(10, Range("cellname2").Column - 1).Value =
CDbl(Calendar1.Value)
End If

Cells(10,nCol).value = textbox2.text

Unload Me
End Sub

--------------------


I have no idea how this code should be written.

Please help me get this code written up correctly.

Thank you for your support


--
oberon.black
------------------------------------------------------------------------
oberon.black's Profile:
http://www.excelforum.com/member.php...o&userid=26732
View this thread: http://www.excelforum.com/showthread...hreadid=468385

Modify Command Button
 

Hey I am all about records, but I still need help solving this problem.


--
oberon.black
------------------------------------------------------------------------
oberon.black's Profile: http://www.excelforum.com/member.php...o&userid=26732
View this thread: http://www.excelforum.com/showthread...hreadid=468385

Modify Command Button
 

If my understanding of your objective as described in my last message is
correct, I don't know how to assist further without a hint as to what your
problem is. Maybe someone will read into your earlier posts something I've
missed.

Regards,
Peter T

"oberon.black"
wrote in message
news:oberon.black.1vk8ab_1127055915.0963@excelforu m-nospam.com...

Hey I am all about records, but I still need help solving this problem.


--
oberon.black
------------------------------------------------------------------------
oberon.black's Profile:
http://www.excelforum.com/member.php...o&userid=26732
View this thread: http://www.excelforum.com/showthread...hreadid=468385

Modify Command Button
 

What I want to know is if this code:


Code
-------------------

Private Sub CommandButton1_Click()
Dim nCol as long

If OptionButton1 = True Then
Range("cellname").EntireColumn.Insert
Worksheets("CGS").Cells(11, Range("cellname").Column - 1).Formula =
TextBox1.Value
nCol = Range("cellname").Column
Worksheets("CGS").Cells(10, Range("cellname").Column - 1).Value =
CDbl(Calendar1.Value)
Else
Range("cellname2").EntireColumn.Insert
Worksheets("CGS").Cells(11, Range("cellname2").Column - 1).Formula =
TextBox1.Value
nCol = Range("cellname2").Column
Worksheets("CGS").Cells(10, Range("cellname2").Column - 1).Value =
CDbl(Calendar1.Value)
End If

Cells(10,nCol).value = textbox2.text

Unload Me
End Sub

-------------------


is properly written to :
add this code (A) :

***'x' is being used as a variable***

Code
-------------------


range("x10").value = textbox2.text


-------------------


to this code (B):


Code
-------------------


Private Sub CommandButton1_Click()
If OptionButton1 = True Then
Range("cellname").EntireColumn.Insert
Worksheets("CGS").Cells(11, Range("cellname").Column - 1).Formula = TextBox1.Value
Worksheets("CGS").Cells(10, Range("cellname").Column - 1).Value =
CDbl(Calendar1.Value)
Else
Range("cellname2").EntireColumn.Insert
Worksheets("CGS").Cells(11, Range("cellname2").Column - 1).Formula =
TextBox1.Value
Worksheets("CGS").Cells(10, Range("cellname2").Column - 1).Value =
CDbl(Calendar1.Value)
End If
Unload Me
End Sub

-------------------


So that the information that is entered into textbox2 will go into th
new column that is created by code (B). Therefore everytime I add a ne
column using code (B) range(x) will be equal to textbox2 and be place
in that new column.

Example

I run code (B) and it creates column 'D'. I want the info that I put i
textbox2 to be entered in 'D10'. Then if I run code (B) again and i
creates column 'E' I want the info that I enter into textbox2 to b
entered into 'E10' and so on. So that everytime I run code(B) th
information in textbox2 to will be entered in to the newcolumn and int
row 10 of that column.

Now do you get what I am trying to do

--
oberon.blac
-----------------------------------------------------------------------
oberon.black's Profile: http://www.excelforum.com/member.php...fo&userid=2673
View this thread: http://www.excelforum.com/showthread.php?threadid=46838

Modify Command Button
 

"oberon.black"
wrote in message
news:oberon.black.1vkjec_1127070314.0797@excelforu m-nospam.com...

What I want to know is if this code:


Code:
--------------------

Private Sub CommandButton1_Click()
Dim nCol as long

If OptionButton1 = True Then
Range("cellname").EntireColumn.Insert
Worksheets("CGS").Cells(11, Range("cellname").Column - 1).Formula =
TextBox1.Value
nCol = Range("cellname").Column
Worksheets("CGS").Cells(10, Range("cellname").Column - 1).Value =
CDbl(Calendar1.Value)
Else
Range("cellname2").EntireColumn.Insert
Worksheets("CGS").Cells(11, Range("cellname2").Column - 1).Formula =
TextBox1.Value
nCol = Range("cellname2").Column
Worksheets("CGS").Cells(10, Range("cellname2").Column - 1).Value =
CDbl(Calendar1.Value)
End If

Cells(10,nCol).value = textbox2.text

Unload Me
End Sub

--------------------


is properly written to :
add this code (A) :

***'x' is being used as a variable***

Code:
--------------------


range("x10").value = textbox2.text


--------------------


to this code (B):


Code:
--------------------


Private Sub CommandButton1_Click()
If OptionButton1 = True Then
Range("cellname").EntireColumn.Insert
Worksheets("CGS").Cells(11, Range("cellname").Column - 1).Formula =
TextBox1.Value
Worksheets("CGS").Cells(10, Range("cellname").Column - 1).Value =
CDbl(Calendar1.Value)
Else
Range("cellname2").EntireColumn.Insert
Worksheets("CGS").Cells(11, Range("cellname2").Column - 1).Formula =
TextBox1.Value
Worksheets("CGS").Cells(10, Range("cellname2").Column - 1).Value =
CDbl(Calendar1.Value)
End If
Unload Me
End Sub

--------------------


So that the information that is entered into textbox2 will go into the
new column that is created by code (B). Therefore everytime I add a new
column using code (B) range(x) will be equal to textbox2 and be placed
in that new column.

Example

I run code (B) and it creates column 'D'. I want the info that I put in
textbox2 to be entered in 'D10'. Then if I run code (B) again and it
creates column 'E' I want the info that I enter into textbox2 to be
entered into 'E10' and so on. So that everytime I run code(B) the
information in textbox2 to will be entered in to the newcolumn and into
row 10 of that column.

Now do you get what I am trying to do?


--
oberon.black
------------------------------------------------------------------------
oberon.black's Profile:
http://www.excelforum.com/member.php...o&userid=26732
View this thread: http://www.excelforum.com/showthread...hreadid=468385

Modify Command Button
 

We are going round in circles. My "understanding" of your objective, as
described previously, has not changed in light of your rephrased example
quoted below. Your revised code, based on my suggestion, should achieve your
objective. As I replied before after you asked the same question.

I note from your code in some cases you reference to Worksheets("CGS"), but
in other lines you make no reference to the worksheet. I assume that
cellname & cellname2 refer to cells on the active sheet. Also that
Worksheets("CGS") is the active sheet, and you want the new value from
textbox2 to go into the active sheet. I did advise previously about
correctly referencing sheets.

Hove you actually tried the code, from what you have copied it does not
appear that you have (otherwise there would be a space after the comma in
"Cells(10,nCol).value".

If you have tried the code try stepping through with F8 and follow things in
the Locals window Ctrl-v,s. On each line check that all the data & object
values indicate what you think they should be.

After doing that give details, eg code breaks on this line..

Regards,
Peter T



"oberon.black"
wrote in message
news:oberon.black.1vkjec_1127070314.0797@excelforu m-nospam.com...

What I want to know is if this code:


Code:
--------------------

Private Sub CommandButton1_Click()
Dim nCol as long

If OptionButton1 = True Then
Range("cellname").EntireColumn.Insert
Worksheets("CGS").Cells(11, Range("cellname").Column - 1).Formula =
TextBox1.Value
nCol = Range("cellname").Column
Worksheets("CGS").Cells(10, Range("cellname").Column - 1).Value =
CDbl(Calendar1.Value)
Else
Range("cellname2").EntireColumn.Insert
Worksheets("CGS").Cells(11, Range("cellname2").Column - 1).Formula =
TextBox1.Value
nCol = Range("cellname2").Column
Worksheets("CGS").Cells(10, Range("cellname2").Column - 1).Value =
CDbl(Calendar1.Value)
End If

Cells(10,nCol).value = textbox2.text

Unload Me
End Sub

--------------------


is properly written to :
add this code (A) :

***'x' is being used as a variable***

Code:
--------------------


range("x10").value = textbox2.text


--------------------


to this code (B):


Code:
--------------------


Private Sub CommandButton1_Click()
If OptionButton1 = True Then
Range("cellname").EntireColumn.Insert
Worksheets("CGS").Cells(11, Range("cellname").Column - 1).Formula =
TextBox1.Value
Worksheets("CGS").Cells(10, Range("cellname").Column - 1).Value =
CDbl(Calendar1.Value)
Else
Range("cellname2").EntireColumn.Insert
Worksheets("CGS").Cells(11, Range("cellname2").Column - 1).Formula =
TextBox1.Value
Worksheets("CGS").Cells(10, Range("cellname2").Column - 1).Value =
CDbl(Calendar1.Value)
End If
Unload Me
End Sub

--------------------


So that the information that is entered into textbox2 will go into the
new column that is created by code (B). Therefore everytime I add a new
column using code (B) range(x) will be equal to textbox2 and be placed
in that new column.

Example

I run code (B) and it creates column 'D'. I want the info that I put in
textbox2 to be entered in 'D10'. Then if I run code (B) again and it
creates column 'E' I want the info that I enter into textbox2 to be
entered into 'E10' and so on. So that everytime I run code(B) the
information in textbox2 to will be entered in to the newcolumn and into
row 10 of that column.

Now do you get what I am trying to do?


--
oberon.black
------------------------------------------------------------------------
oberon.black's Profile:
http://www.excelforum.com/member.php...o&userid=26732
View this thread: http://www.excelforum.com/showthread...hreadid=468385

Modify Command Button
 

I have indeed tried this code, and after trying it nothing
happens....and I mean absolutly nothing the orginal code does not even
work any more. Yet I do not get any error msgs.

I am very new at this code writing. I am simply trying to piece the
puzzle together based on information that I recieve from these posting
as well as conversations.

Please continue to provide me with information. Yours and everyone
elses help has and will continued to be welcome and I seriously can not
thank ya'll enough for the help.

I noticed you mentioned putting a space after the comma?


--
oberon.black
------------------------------------------------------------------------
oberon.black's Profile: http://www.excelforum.com/member.php...o&userid=26732
View this thread: http://www.excelforum.com/showthread...hreadid=468385

Modify Command Button
 

OK the orginal code does still work but the addt'l code does not add th
text from textbox2 into the desired column or row or any where

--
oberon.blac
-----------------------------------------------------------------------
oberon.black's Profile: http://www.excelforum.com/member.php...fo&userid=2673
View this thread: http://www.excelforum.com/showthread.php?threadid=46838

Modify Command Button
 

Include the following near the end of your code for testing

With Cells(10,nCol)
Msgbox .Parent.name & " " & .address & vbcr & textbox2.text

Cells(10,nCol).value = textbox2.text '
Msgbox .Value
End With

'Unload me ' comment this and continue testing. Unload with the small X top
right.

Regards,
Peter T


"oberon.black"
wrote in message
news:oberon.black.1vkuib_1127084712.1857@excelforu m-nospam.com...

OK the orginal code does still work but the addt'l code does not add the
text from textbox2 into the desired column or row or any where.


--
oberon.black
------------------------------------------------------------------------
oberon.black's Profile:
http://www.excelforum.com/member.php...o&userid=26732
View this thread: http://www.excelforum.com/showthread...hreadid=468385

Modify Command Button
 

Ok, this is my current code:


Code:
--------------------

Private Sub CommandButton1_Click()
Dim nCol As Long

If OptionButton1 = True Then
Range("D").EntireColumn.Insert
Worksheets("CGS").Cells(11, Range("D").Column - 1).Formula = TextBox1.Value
nCol = Range("D").Column
Worksheets("Class GradeSheet").Cells(10, Range("D").Column - 1).Value = CDbl(Calendar1.Value)
Else
Range("T").EntireColumn.Insert
Worksheets("CGS").Cells(11, Range("T").Column - 1).Formula = TextBox1.Value
nCol = Range("T").Column
Worksheets("CGS").Cells(10, Range("T").Column - 1).Value = CDbl(Calendar1.Value)
End If

With Cells(12, nCol)
MsgBox .Parent.Name & " " & .address & vbCr & TextBox42.Text

Cells(12, nCol).Value = TextBox42.Text '
MsgBox .Value
End With

Unload Me
End Sub

--------------------


this code works, but instead of putting the number in the row 12 of the
new column it puts the number in row 12 of the column with the name
range 'D' or 'T'.
Which after reading the code I do understand why.

What I need to know how to do is have the information from textbox42
placed in row 12 of the column before the one with the name range 'D'
or 'T'?


--
oberon.black
------------------------------------------------------------------------
oberon.black's Profile: http://www.excelforum.com/member.php...o&userid=26732
View this thread: http://www.excelforum.com/showthread...hreadid=468385

Modify Command Button
 

Unless I'm missing something the new code looks same as previous except for
renamed cells and textbox. Are you saying everything now works fine except
you want the value to go one column to the left?

If so simply change-
Cells(12, nCol).Value = TextBox42.Text
to
Cells(12, nCol -1).Value = TextBox42.Text

This would place the value in the original column of one of the named cells,
before the named cell was moved to the right with .EntireColumn.Insert.

Instead of the above amendment you could get "nCol" before the insertion by
moving
nCol = Range("D").Column & equivalent for the "T" cell
above the respective lines that do the insert.

Regards,
Peter T

"oberon.black"
wrote in message
news:oberon.black.1vl8ef_1127102984.6709@excelforu m-nospam.com...

Ok, this is my current code:


Code:
--------------------

Private Sub CommandButton1_Click()
Dim nCol As Long

If OptionButton1 = True Then
Range("D").EntireColumn.Insert
Worksheets("CGS").Cells(11, Range("D").Column - 1).Formula =
TextBox1.Value
nCol = Range("D").Column
Worksheets("Class GradeSheet").Cells(10, Range("D").Column - 1).Value =
CDbl(Calendar1.Value)
Else
Range("T").EntireColumn.Insert
Worksheets("CGS").Cells(11, Range("T").Column - 1).Formula =
TextBox1.Value
nCol = Range("T").Column
Worksheets("CGS").Cells(10, Range("T").Column - 1).Value =
CDbl(Calendar1.Value)
End If

With Cells(12, nCol)
MsgBox .Parent.Name & " " & .address & vbCr & TextBox42.Text

Cells(12, nCol).Value = TextBox42.Text '
MsgBox .Value
End With

Unload Me
End Sub

--------------------


this code works, but instead of putting the number in the row 12 of the
new column it puts the number in row 12 of the column with the name
range 'D' or 'T'.
Which after reading the code I do understand why.

What I need to know how to do is have the information from textbox42
placed in row 12 of the column before the one with the name range 'D'
or 'T'?


--
oberon.black
------------------------------------------------------------------------
oberon.black's Profile:
http://www.excelforum.com/member.php...o&userid=26732
View this thread: http://www.excelforum.com/showthread...hreadid=468385

Modify Command Button
 

ncol -1 worked perfectly.

Now the final part of this problem is that I need to have the
information duplicated to other sheets in the workbook.

Like so:

I have this code (A) which adds a clients name to the current sheet, a
sheet called AT, and it creates a new sheet in the workbook titled with
the clients name. i.e. Smith, James.

code (A)

Code:
--------------------

Private Sub cmdAdd_Click()
Dim iRow As Long
Dim ws As Worksheet
Dim newSheetName As String
Dim ws1 As Worksheet
Dim iRow1 As Long
Set ws = Worksheets("CGS")
Set ws1 = Worksheets("AT")

'find first empty row in AT, column 2
If ws1.Range("B10").Value = "" Then
iRow1 = ws1.Cells(Rows.Count, 2) _
.End(xlUp).Row + 9 'adjust if there is data _'in B1:B9
Else
iRow1 = ws1.Cells(Rows.Count, 2) _
.End(xlUp).Row + 1
End If

'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row

'check for a part number
If Trim(Me.LstNm.Value) = "" Then
Me.LstNm.SetFocus
MsgBox "Please enter last name"
Exit Sub
End If

'copy the data to the database
ws.Cells(iRow, 1).Value = Me.LstNm.Value
ws.Cells(iRow, 5).Value = Me.FrstNm.Value
ws1.Cells(iRow, 2).Value = Me.LstNm.Value
ws1.Cells(iRow, 6).Value = Me.FrstNm.Value
newSheetName = ws.Cells(iRow, 1) & "," & ws.Cells(iRow, 5)

'clear the data
Me.LstNm.Value = ""
Me.FrstNm.Value = ""
Me.LstNm.SetFocus

For Each ws In Worksheets
If ws.Name = newSheetName Or _
newSheetName = "" Or _
IsNumeric(newSheetName) Then
MsgBox "Sheet already exists or name is invalid", vbInformation
Exit Sub
End If

Next
Sheets("Client Sheet").Visible = xlSheetVisible
Sheets("Client Sheet").Copy befo=Sheets(1)
Sheets("Client Sheet").Visible = xlSheetVeryHidden
Sheets(1).Name = newSheetName
Sheets(newSheetName).Move After:=Sheets(Sheets.Count)

'close userform
Unload Me

'copy data to AT (ws1) at the same time you copy to database.
Worksheets("CGS").Activate

End Sub

--------------------


Now my second code (B) of course you know gathers information from the
sheet, inserts a column and adds information to a columns rows. The
info that the userform gets from the sheet are the client names, I then
enter in the data needed in the textbox nest to that clients name and
then submit it back it to the sheet via the user userform with this
code (B).

code (B)

Code:
--------------------

Private Sub CommandButton1_Click()
Dim nCol As Long

If OptionButton1 = True Then
Range("D").EntireColumn.Insert
Worksheets("CGS").Cells(11, Range("D").Column - 1).Formula = TextBox1.Value
nCol = Range("D").Column
Worksheets("CGS").Cells(10, Range("D").Column - 1).Value = CDbl(Calendar1.Value)
Else
Range("T").EntireColumn.Insert
Worksheets("CGS").Cells(11, Range("T").Column - 1).Formula = TextBox1.Value
nCol = Range("T").Column
Worksheets("CGS").Cells(10, Range("T").Column - 1).Value = CDbl(Calendar1.Value)
End If
'cell and text placement
Cells(12, nCol - 1).Value = TextBox42.Text
Cells(13, nCol - 1).Value = TextBox43.Text
Cells(14, nCol - 1).Value = TextBox44.Text
Cells(15, nCol - 1).Value = TextBox45.Text
Cells(16, nCol - 1).Value = TextBox46.Text
Cells(17, nCol - 1).Value = TextBox47.Text
Cells(18, nCol - 1).Value = TextBox48.Text
Cells(19, nCol - 1).Value = TextBox49.Text
Cells(20, nCol - 1).Value = TextBox50.Text
Cells(21, nCol - 1).Value = TextBox51.Text
Cells(22, nCol - 1).Value = TextBox52.Text
Cells(23, nCol - 1).Value = TextBox53.Text
Cells(24, nCol - 1).Value = TextBox54.Text
Cells(25, nCol - 1).Value = TextBox55.Text
Cells(26, nCol - 1).Value = TextBox56.Text
Cells(27, nCol - 1).Value = TextBox57.Text
Cells(28, nCol - 1).Value = TextBox58.Text
Cells(29, nCol - 1).Value = TextBox59.Text
Cells(30, nCol - 1).Value = TextBox60.Text
Cells(31, nCol - 1).Value = TextBox61.Text
Cells(32, nCol - 1).Value = TextBox62.Text
Cells(33, nCol - 1).Value = TextBox63.Text
Cells(34, nCol - 1).Value = TextBox64.Text
Cells(35, nCol - 1).Value = TextBox65.Text
Cells(36, nCol - 1).Value = TextBox66.Text
Cells(37, nCol - 1).Value = TextBox67.Text
Cells(38, nCol - 1).Value = TextBox68.Text
Cells(39, nCol - 1).Value = TextBox69.Text
Cells(40, nCol - 1).Value = TextBox70.Text
Cells(41, nCol - 1).Value = TextBox71.Text
Cells(42, nCol - 1).Value = TextBox72.Text
Cells(43, nCol - 1).Value = TextBox73.Text
Cells(44, nCol - 1).Value = TextBox74.Text
Cells(45, nCol - 1).Value = TextBox75.Text
Cells(46, nCol - 1).Value = TextBox76.Text
Cells(47, nCol - 1).Value = TextBox77.Text
Cells(48, nCol - 1).Value = TextBox78.Text
Cells(49, nCol - 1).Value = TextBox79.Text
Cells(50, nCol - 1).Value = TextBox80.Text
Cells(51, nCol - 1).Value = TextBox81.Text
'close form
Unload Me
End Sub

--------------------


Now what I need to do is get the information that is found in 'B10' and
'B11' of the 'CGS' worksheet to copy over to the client worksheets and
then get all off the information that each client has on there row
copied over to there worksheet.

example:
view from CGS
---------------------------------Date (B10)-----------Date
(C10).............
---------------------------------Income (B11)--------Expense
(C11).......
Smith, James (client)-------1,500 (B12)----------1,800
(C12)............


I would like for it to display as follows in (client worksheet) Smith,
James
Date (A12)-------Income (B12)----------------1500 (C12).........
Date (A13)-------Expense (B13)--------------1800 (C13)..........


So this is what I now have to accomplish. This has been a huge task
for someone how does not code.

Please continue to support me in my quest to create something great.


--
oberon.black
------------------------------------------------------------------------
oberon.black's Profile: http://www.excelforum.com/member.php...o&userid=26732
View this thread: http://www.excelforum.com/showthread...hreadid=468385

Modify Command Button
 

still looking for help on this one.


--
oberon.black
------------------------------------------------------------------------
oberon.black's Profile: http://www.excelforum.com/member.php...o&userid=26732
View this thread: http://www.excelforum.com/showthread...hreadid=468385

Modify Command Button
 

I've no doubt you have explained what you now want to do but trying to
understand that from all the code you have posted, without recreating your
workbook, sheet names & userform etc, will take me a long time.

Can you not post a simplified example of what you are looking for.

Regards,
Peter T

"oberon.black"
wrote in message
news:oberon.black.1vmxic_1127181925.6347@excelforu m-nospam.com...

ncol -1 worked perfectly.

Now the final part of this problem is that I need to have the
information duplicated to other sheets in the workbook.

Like so:

I have this code (A) which adds a clients name to the current sheet, a
sheet called AT, and it creates a new sheet in the workbook titled with
the clients name. i.e. Smith, James.

code (A)

Code:
--------------------

Private Sub cmdAdd_Click()
Dim iRow As Long
Dim ws As Worksheet
Dim newSheetName As String
Dim ws1 As Worksheet
Dim iRow1 As Long
Set ws = Worksheets("CGS")
Set ws1 = Worksheets("AT")

'find first empty row in AT, column 2
If ws1.Range("B10").Value = "" Then
iRow1 = ws1.Cells(Rows.Count, 2) _
.End(xlUp).Row + 9 'adjust if there is data _'in B1:B9
Else
iRow1 = ws1.Cells(Rows.Count, 2) _
.End(xlUp).Row + 1
End If

'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row

'check for a part number
If Trim(Me.LstNm.Value) = "" Then
Me.LstNm.SetFocus
MsgBox "Please enter last name"
Exit Sub
End If

'copy the data to the database
ws.Cells(iRow, 1).Value = Me.LstNm.Value
ws.Cells(iRow, 5).Value = Me.FrstNm.Value
ws1.Cells(iRow, 2).Value = Me.LstNm.Value
ws1.Cells(iRow, 6).Value = Me.FrstNm.Value
newSheetName = ws.Cells(iRow, 1) & "," & ws.Cells(iRow, 5)

'clear the data
Me.LstNm.Value = ""
Me.FrstNm.Value = ""
Me.LstNm.SetFocus

For Each ws In Worksheets
If ws.Name = newSheetName Or _
newSheetName = "" Or _
IsNumeric(newSheetName) Then
MsgBox "Sheet already exists or name is invalid", vbInformation
Exit Sub
End If

Next
Sheets("Client Sheet").Visible = xlSheetVisible
Sheets("Client Sheet").Copy befo=Sheets(1)
Sheets("Client Sheet").Visible = xlSheetVeryHidden
Sheets(1).Name = newSheetName
Sheets(newSheetName).Move After:=Sheets(Sheets.Count)

'close userform
Unload Me

'copy data to AT (ws1) at the same time you copy to database.
Worksheets("CGS").Activate

End Sub

--------------------


Now my second code (B) of course you know gathers information from the
sheet, inserts a column and adds information to a columns rows. The
info that the userform gets from the sheet are the client names, I then
enter in the data needed in the textbox nest to that clients name and
then submit it back it to the sheet via the user userform with this
code (B).

code (B)

Code:
--------------------

Private Sub CommandButton1_Click()
Dim nCol As Long

If OptionButton1 = True Then
Range("D").EntireColumn.Insert
Worksheets("CGS").Cells(11, Range("D").Column - 1).Formula =
TextBox1.Value
nCol = Range("D").Column
Worksheets("CGS").Cells(10, Range("D").Column - 1).Value =
CDbl(Calendar1.Value)
Else
Range("T").EntireColumn.Insert
Worksheets("CGS").Cells(11, Range("T").Column - 1).Formula =
TextBox1.Value
nCol = Range("T").Column
Worksheets("CGS").Cells(10, Range("T").Column - 1).Value =
CDbl(Calendar1.Value)
End If
'cell and text placement
Cells(12, nCol - 1).Value = TextBox42.Text
Cells(13, nCol - 1).Value = TextBox43.Text
Cells(14, nCol - 1).Value = TextBox44.Text
Cells(15, nCol - 1).Value = TextBox45.Text
Cells(16, nCol - 1).Value = TextBox46.Text
Cells(17, nCol - 1).Value = TextBox47.Text
Cells(18, nCol - 1).Value = TextBox48.Text
Cells(19, nCol - 1).Value = TextBox49.Text
Cells(20, nCol - 1).Value = TextBox50.Text
Cells(21, nCol - 1).Value = TextBox51.Text
Cells(22, nCol - 1).Value = TextBox52.Text
Cells(23, nCol - 1).Value = TextBox53.Text
Cells(24, nCol - 1).Value = TextBox54.Text
Cells(25, nCol - 1).Value = TextBox55.Text
Cells(26, nCol - 1).Value = TextBox56.Text
Cells(27, nCol - 1).Value = TextBox57.Text
Cells(28, nCol - 1).Value = TextBox58.Text
Cells(29, nCol - 1).Value = TextBox59.Text
Cells(30, nCol - 1).Value = TextBox60.Text
Cells(31, nCol - 1).Value = TextBox61.Text
Cells(32, nCol - 1).Value = TextBox62.Text
Cells(33, nCol - 1).Value = TextBox63.Text
Cells(34, nCol - 1).Value = TextBox64.Text
Cells(35, nCol - 1).Value = TextBox65.Text
Cells(36, nCol - 1).Value = TextBox66.Text
Cells(37, nCol - 1).Value = TextBox67.Text
Cells(38, nCol - 1).Value = TextBox68.Text
Cells(39, nCol - 1).Value = TextBox69.Text
Cells(40, nCol - 1).Value = TextBox70.Text
Cells(41, nCol - 1).Value = TextBox71.Text
Cells(42, nCol - 1).Value = TextBox72.Text
Cells(43, nCol - 1).Value = TextBox73.Text
Cells(44, nCol - 1).Value = TextBox74.Text
Cells(45, nCol - 1).Value = TextBox75.Text
Cells(46, nCol - 1).Value = TextBox76.Text
Cells(47, nCol - 1).Value = TextBox77.Text
Cells(48, nCol - 1).Value = TextBox78.Text
Cells(49, nCol - 1).Value = TextBox79.Text
Cells(50, nCol - 1).Value = TextBox80.Text
Cells(51, nCol - 1).Value = TextBox81.Text
'close form
Unload Me
End Sub

--------------------


Now what I need to do is get the information that is found in 'B10' and
'B11' of the 'CGS' worksheet to copy over to the client worksheets and
then get all off the information that each client has on there row
copied over to there worksheet.

example:
view from CGS
---------------------------------Date (B10)-----------Date
(C10).............
---------------------------------Income (B11)--------Expense
(C11).......
Smith, James (client)-------1,500 (B12)----------1,800
(C12)............


I would like for it to display as follows in (client worksheet) Smith,
James
Date (A12)-------Income (B12)----------------1500 (C12).........
Date (A13)-------Expense (B13)--------------1800 (C13)..........


So this is what I now have to accomplish. This has been a huge task
for someone how does not code.

Please continue to support me in my quest to create something great.


--
oberon.black
------------------------------------------------------------------------
oberon.black's Profile:
http://www.excelforum.com/member.php...o&userid=26732
View this thread: http://www.excelforum.com/showthread...hreadid=468385

Modify Command Button
 

Sorry, I thought it would be easier if you saw everything.

What I need to do is get the information that is found in 'B10' an
'B11' of the 'CGS' worksheet to copy over to the client worksheets an
then get all of the information that each client has on there ro
copied over to there worksheet.

example:
view from CGS
---------------------------------Date (B10)-----------Dat
(C10).............
---------------------------------Income (B11)--------Expens
(C11).......
Smith, James (client)-------1,500 (B12)----------1,80
(C12)............
Johnson, Mark (client)------1450 (B13)----------132
(C13).............


I would like for it to display as follows in (client worksheet)

Smith, James (worksheet)
Date (A12)-------Income (B12)----------------1500 (C12).........
Date (A13)-------Expense (B13)--------------1800 (C13)..........

Johnson,Mark (worksheet)
Date (A12)-------Income (B12)----------------1450 (C12).........
Date (A13)-------Expense (B13)--------------1321 (C13).........

--
oberon.blac
-----------------------------------------------------------------------
oberon.black's Profile: http://www.excelforum.com/member.php...fo&userid=2673
View this thread: http://www.excelforum.com/showthread.php?threadid=46838

Modify Command Button
 

Still looking for some help


--
oberon.black
------------------------------------------------------------------------
oberon.black's Profile: http://www.excelforum.com/member.php...o&userid=26732
View this thread: http://www.excelforum.com/showthread...hreadid=468385

Modify Command Button
 

I need some of the big time brains in here to help me out this is a rea
jam for me

--
oberon.blac
-----------------------------------------------------------------------
oberon.black's Profile: http://www.excelforum.com/member.php...fo&userid=2673
View this thread: http://www.excelforum.com/showthread.php?threadid=46838

Modify Command Button
 

I don't follow follow where the data is on the CGS sheet and where it should
go on the client sheet. Yes I know you gave specific examples but the code
doesn't know at the onset the particular locations, and needs to work it out
from information you provide. Also it seems data eventually goes into 2 rows
on the client sheet, which means you need to keep track of where everything
is.

Put your big project aside and try something simple yourself to work out the
logic. Eventually you might be able to do something like this.

Dim wsClient as worksheet
Dim sClients(0 as String
'code

Redim sClients(1 to numberOfClients)
'loop to populate sClients array from cells

sClents(1) = "Smith"

set wsClient = Workbook("Clients").worksheets(sClients(1))

wsClient.Range(cells(rowCurrent, col1), cells(rowCurrent, col1 + 5).Value =
_
Worksheets("CGS")..Range(cells(rowX, colY), cells(rowX, colY + 5).Value

- probably in a loop where you know the variable row & column numbers

With all bumps you keep adding to this thread I get the impression you are
going to demand a refund.

Regards,
Peter T

"oberon.black"
wrote in message
news:oberon.black.1vns2e_1127221529.3058@excelforu m-nospam.com...

Sorry, I thought it would be easier if you saw everything.

What I need to do is get the information that is found in 'B10' and
'B11' of the 'CGS' worksheet to copy over to the client worksheets and
then get all of the information that each client has on there row
copied over to there worksheet.

example:
view from CGS
---------------------------------Date (B10)-----------Date
(C10).............
---------------------------------Income (B11)--------Expense
(C11).......
Smith, James (client)-------1,500 (B12)----------1,800
(C12)............
Johnson, Mark (client)------1450 (B13)----------1321
(C13).............


I would like for it to display as follows in (client worksheet)

Smith, James (worksheet)
Date (A12)-------Income (B12)----------------1500 (C12).........
Date (A13)-------Expense (B13)--------------1800 (C13)..........

Johnson,Mark (worksheet)
Date (A12)-------Income (B12)----------------1450 (C12).........
Date (A13)-------Expense (B13)--------------1321 (C13)..........


--
oberon.black
------------------------------------------------------------------------
oberon.black's Profile:
http://www.excelforum.com/member.php...o&userid=26732
View this thread: http://www.excelforum.com/showthread...hreadid=468385