Looking Up A Data Entry And Change A Cell Using VBA

[Tommy2001]

Hi All. I am struggling with a problem using VBA code. I am self taught using helpful sites like this and YouTube videos.

I am trying to write a line of code that will search a table of data based on a cell and change the status of a another cell.

So the user will fill out the form below and the data will save to a second page (further down) when they use either "Submit Request" or "Save For Later" command buttons.

As you can see, Column J shows the status of the order. There will be lots of entries that are processed at different times.

When the user submits the request after it has been "Saved For Later", I would like it to look up the line and change the status to Submitted.

I hope that all makes sense. Any help would be greatly appreciated.

P.S if there is any online courses that you would recommend to help me sharpen my Excel skills, please let me know.

 

[Rhodie72]

Personally, I wouldn't bother with a spreadsheet. In this case I would build a front-end application and everything would operate from there. The data may be stored in a spreadsheet but the functionality would be like a standalone custom application with use of Userforms.

 

[Rhodie72]

The key would be the purchase order number.

Code it to .Activate the sheet you want it to find the status from and then reference the number of cells over to the PO number when it finds what you are wanting it to find with something like

 selection.offset(-6,0).value

 

[Vogateer]

I'm pretty sure I'm not understanding how the user is using this. It sounds like the user would reenter all the information and hit Submit? But this doesn't make sense, because then the Save For Later button is just wasting effort. Is there some reason the user can't search for the Not Submitted entries and change them there?

Also, I'm not sure whether you're determined to do this all in Excel, but if you want to try something outside of Excel as Rhodie72 mentions, I'd recommend looking into something like Grist. It's a database that you can use like Excel, and it looks like it might handle what you're doing much better than Excel can.

 

[Rhodie72]

I'm pretty sure I'm not understanding how the user is using this. It sounds like the user would reenter all the information and hit Submit? But this doesn't make sense, because then the Save For Later button is just wasting effort. Is there some reason the user can't search for the Not Submitted entries and change them there?

Also, I'm not sure whether you're determined to do this all in Excel, but if you want to try something outside of Excel as Rhodie72 mentions, I'd recommend looking into something like Grist. It's a database that you can use like Excel, and it looks like it might handle what you're doing much better than Excel can.

A VBA frontend is what I'm talking about