Find and past button

[_Fly_]

a

b

c

d

e

f

g

h

i

1

Number

2

Item

3

Stock

4

Condition

5

<tbody>
</tbody>

Sheet Entrance

	a	b	c	d
1	Number	Item	Stock	Condition
2	123456	Paper Case	100	New
3	234123	Chair	4	Used

<tbody>
</tbody>

Sheet Global

Hi my friends it's possible to help me on that issue?
I want to put a code on my button that appears on the Sheet Entrance that when i click appears a msgbox aske me for a number. Then i put the number and click ok, then the code searches on the sheet global for that code and if that number exists copy the interior of the correspondent cell on the column a, b, c and d to the Sheet entrance on the correspondent cells C1, C2, C3, C3. If the number doens't exists appear that info.

Example i click the button on the sheet Entrance and a msgbox appear ask me a number i insert the 234123 and click ok.
The code goes search for that number on A:A and finds on the A3 cell, so it copys the a3 value from Sheet Global to C1 Sheet Entrance, the b3 value from Sheet Global to the c2 Sheet Entrance, the c3 value from Sheet Global to the d3 Sheet Entrance, the d3 value from Sheet Global to the c4Sheet Entrance.

It's possible to give me any ideas to the code?
Regards

 

[_Fly_]

Howard doesn't work... when i put a value bigger than the stock available and i click the Inserir button appears a negative value and no advice i think when i click the Inserir Button the code must simply compare the value of N10 and F10 and if F10 is bigger then N10 i should appear the advice that only can remove at maximum the N10 value...
You haven't solved the formatting issue ( on the copy paste everything goes crazy on the Saidas sheet...)

Regards

 

[_Fly_]

Howard i have an idea.
After click the Procuar button appears the inputbox that you create to insert the code. I insert that code and press ok then the fields on the Saidas sheets it should be filled as usual but after that appears a new inputbox that inform me: "Actual there are XX itens, please insert the number of itens you want to remove.
I insert that number and that number shoud be inferior or equal then N10 value on Saidas, if not appears the adviceyou already created and id the number is equal or inferior to N10 then that number of the inputbox will be inserted on F10.
That way the Inserir advices will be not necessary anymore.

REgards

 

[L. Howard]

I'll see what I can do.

You want another input box in the Procuar code that asks for the number to be inserted in Quantidade Retirada: F10 on the Saidas sheets.

Howard

 

[_Fly_]

Yes insted of select F10 and put a number se the order

1- Click button procurar
2- Appears the actual input box
3 - Insert a number on that input box and click ok
4 - if the number exists the values of the Registos Globais Sheet will fill the respective places on the Saidas sheet
- Until now you have already create the code and works well -
5 - After the fields on the Saidas sheet was filled appears a new input box that informs that the stock available was xx (n10 value), and ask me the quantity of items to remove
6 - When i click the ok of that input box and if the inserted number was superior than N10 value appears an advice telling that i only can remove the N10 value
if the number was equal or inferior than N10 that value will be copied to F10 cell
7- I click on the Inserir button and because the values are already confirmed on the before input box the value of new N10 (after remove) will be copied to Registos globais sheet (that you have already done....)
Thank's

 

[L. Howard]

Okay, I am working on it.

Howard

 

[L. Howard]

Hi Fly,

Give this a try.

Click button procurer and follow the prompts.

I am using a VLOOKUP in cell N10 on Saidas sheet.

The button Inserir may not be needed.

I also am looking for a way to escape the second Input Box (sNum = InputBox...) in the event you want to use CANCEL or the red X to escape. Right now it throws an error and I am having trouble fixing it. Works OK as long as you don't want to quit with that Input Box on the screen.

Howard


https://www.dropbox.com/s/9ofyz47xza0r48q/Stock Materiais3.xlsm?dl=0

 

[L. Howard]

Hi Fly,

Here is a slightly improved version 4


https://www.dropbox.com/s/d7jj8rbx9a8x6l0/Stock Materiais4.xlsm?dl=0

Howard

 

[_Fly_]

Howard
v4 isn't a good idea to me, but thank's any way.
I have played with the code and created that: https://drive.google.com/file/d/0B1UTQJwIdfeaMENHNTk1LWIwNTg/view?usp=sharing
But now i have two questions on the inputbox the last one how can i change the title and insert a vbexclamation?
Second one how can i resolve the problem that everytime i click on the procurar button the Saídas sheet becomes with Registos Globais sheet formation?

Best regards

 

[_Fly_]

Howard i think i have solved almost everything see my last version :https://drive.google.com/file/d/0B1UTQJwIdfeaQVBVeHRQc1RDdTQ/view?usp=sharing

Only haven't solved the formatting question. Any idea?

 

[L. Howard]

Second one how can i resolve the problem that everytime i click on the procurar button the Saídas sheet becomes with Registos Globais sheet formation?

What is the formatting problem?

Is it the cells F6, F6, F10, F12, J10, J12, N10 alignment?

If so, then put this code just above the very last End With in the Private Sub botão_procurar_Click() sub.

Private Sub botão_procurar_Click() 
(.... all the code etc....)

[B]With Range("F6,F10,F12,J10,J12,N10")
       .HorizontalAlignment = xlCenter
       .VerticalAlignment = xlCenter
 End With
 With Range("F8:N8")
       .HorizontalAlignment = xlCenterAcrossSelection
 End With[/B]

End With
End Sub

Howard