Basic code required please

[ipbr21054]

Hi,

I have a worksheet called Sheet1
On sheet1 is a Combobox1 which will have the names inside.
These names are on a worksheet called TYPE and cells A1:A5

I would like the user to select a name then depending on the selection put a letter in cell P7

Here is the names & letter assigned to that name.

ANDY = G
BOB = X
CHARLIE = N
DAVID = M

So basically if David is selected then put the letter M in cell P7

Thanks very much.

 

[Logit]

.
I used a dropdown in C2 on Sheet1, referencing the list of names.

Then in P7 this formula :

=IF(C2="ANDY","G",IF(C2="BOB","X",IF(C2="CHARLIE","N",IF(C2="DAVID","M",""))))

 

[ipbr21054]

Hi,
Ive done the above but when i select the name in the drop down i see no change in P7

If i also click cell P7 the formula that i just put in address bar isnt shown anymore ?

 

[ipbr21054]

OK
Now sorted.

I erased the first character in the address bar and now ok.

Thanks its very nice

 

[Logit]

.
Glad to help. You are welcome.

 

[ipbr21054]

Hi,
Can you advise please.

Using the code above works fine BUT then my code which works from a command button stops working.
If i enter a letter manually into cell P7 then use then command button my code works.
If i select from the drop down list which puts the letter into the cell my code stops working.

Its only a simple code but something to do with how the letter gets into cdell P7

Private Sub CommandButton1_Click()Sheets("Sheet1").Range("P6:U6").Copy Sheets("Sheet1").Range("E6")
Sheets("Sheet1").Range("P7").Copy Sheets("Sheet1").Range("E7")


Sheets("Sheet1").Range("E6:J6").Copy Sheets("DR SITE").Range("E6")
Sheets("Sheet1").Range("E7").Copy Sheets("DR SITE").Range("E7")


Sheets("Sheet1").Range("E6:J6").Copy Sheets("EBAY").Range("E6")
Sheets("Sheet1").Range("E7").Copy Sheets("EBAY").Range("E7")


Sheets("DR SITE").Range("E7").Font.Color = vbWhite
Sheets("DR SITE").Range("E7").Borders.LineStyle = xlNone


Sheets("EBAY").Range("E7").Font.Color = vbWhite
Sheets("EBAY").Range("E7").Borders.LineStyle = xlNone




Sheets("DR SITE").Activate
ActiveSheet.Range("E7").Select


Sheets("EBAY").Activate
ActiveSheet.Range("E7").Select


Sheets("Sheet1").Activate
ActiveSheet.Range("P6").Select


ActiveWorkbook.Save


End Sub

 

[Logit]

.
If you use the code previously given along with the Formula in P7 ... then
use the following code in conjunction with the other code behind your command button, it will transfer only the VALUES
from P7 to E7 :

Dim CopyRng As Range, PasteRng As Range


Set CopyRng = Me.Range("P7")
Set PasteRng = Me.Range("E7")
CopyRng.Copy
PasteRng.PasteSpecial xlPasteValuesAndNumberFormats
PasteRng.PasteSpecial xlPasteFormats
Application.CutCopyMode = False
End Sub