commandbutton on click update textbox1

[Patriot2879]

Hi hope you can help me please, I have the following code below, where when command button is clicked on I want it to auto update textbox1 with the data it finds in sheet 5, Colum A and row 1, can you help me please?

Private Sub CommandButton5_AfterUpdate()
   Dim Fnd As Range
   
   Set Fnd = Sheets("Sheet5").Range("A:A").Find(CommandButton5.Value, , , xlWhole, , , False, , False)
   If Not Fnd Is Nothing Then
      TextBox1.Value = Fnd.Offset(, 1).Value
   End If
   
End Sub

 

[Patriot2879]

Re: commandbutton on click update textbox1 help

hi its 2016, shall I change it to click?

 

[NoSparks]

Re: commandbutton on click update textbox1 help

Well, the title you put on this thread is commandbutton on click.

 

[Patriot2879]

Re: commandbutton on click update textbox1 help

hi have changed it to click now please see code below but this comes up with a run time error 13 - type mismatch and the code is highlighted in yellow

Private Sub CommandButton5_Click()
TextBox1.Value = Sheets(Sheet5).Range("A1").Value
End Sub

 

[NoSparks]

Re: commandbutton on click update textbox1 help

put the sheet name in double quotes

 

[Patriot2879]

Re: commandbutton on click update textbox1 help

great thank you that works , thanks for your patience but I am pretty new to this

 

[NoSparks]

Re: commandbutton on click update textbox1 help

No problem, we all had to start somewhere

 

[Patriot2879]

Re: commandbutton on click update textbox1 help

Thank you , just one more quick query please , in some cells that i want to copy across to the text box are onyl colour coded, for example cell in B2 is coloured in BLUE, but when i put the code in the colour doesn't copy over into the text box is this possible to do at all please? thanks again for everything

 

[My Aswer Is This]

Re: commandbutton on click update textbox1 help

Try this:

'Modified  10/5/2018  11:45:38 AM  EDT
TextBox1.Value = Sheets(3).Range("A1").Value
TextBox1.BackColor = Sheets(3).Range("A1").DisplayFormat.Interior.Color

 

[Patriot2879]

Re: commandbutton on click update textbox1 help

thank you very much this works great

 

[My Aswer Is This]

Re: commandbutton on click update textbox1 help

thank you very much this works great

Glad I was able to help you.