Turn spreadsheet calculation into User form or App

[gazmoz17]

Hi,

I have no experience of userfroms. Is it possible to make columns A:E a user form or look more formal and app like?

Ideally so users could drag it around on desktop as its own little popup window?

Am I able to post the actual file as an attachement?

Its basically an input calculation looking up postcodes and index/match to 2 lookup tables.



Many Thanks
Gareth

 

[sxhall]

Hi gazmoz17,

It is possible to put columns A:E in to a userform, and this would appear to be a popup when called via macro/button. The table can be used to populate the result based on the inputs from the form as well.

Some users use dropbox to host a file, or the XL22b facility on Mr Excel.

From your images I've got...

• user can only change values in column B
• user can change 'Fuel' value in column E
• values in column E are driven by inputs in column B

Also what determines the 'Red' in column C once a postcode has been entered? Is this another lookup to another table?

If you can post the sheet I'll have a look at it for you


sxhall

 

[gazmoz17]

Hi sxhall,

Thanks for the reply really appreciate any help.

Yeah your observations are correct there with regards inputs. Yes red is determined by a lookup in another sheet.

Can ignore the yellows in the image as jsut reference really.

Struggling a little bit with XL22B but not sure will be able to save the whole workbook and vba via XL22B?

Is there another way to post my workbook?... the drop box method you mentioned if you have any more details please?

Many Thanks

 

[gazmoz17]

When installing XL22B Im getting this message:

"This file type is not supported in Protected View."

But I'm not in Protected View as far as I know?

Thanks

 

[sxhall]

You will need to create an account with dropbox.com , there is a try it for free option on the home page.

Once you have an account you can save your file (you can drag and drop the file to the web page when prompted or select the file to upload) and it will be stored there. You can then share a link to the file here that will allow me to access the file via the web.

Sounds like the file will be too large for the XL22b method.

Dropbox is secure and is used here regulalry. I will use it to share the file back to you once done

 

[gazmoz17]

Hi,

Apologies for the dealy I did read this last night at home but only had the file on work PC.

postcode macro-dropbox.xlsm

Shared with Dropbox

www.dropbox.com

Hugely appreciate your assistance. Let me know if you need anything else or the file is incorrectly shared etc.

Managed to edit Trust centre settings and & get XL2BB working and it look awesome tool. Your right though my file is prob too big.

Thanks

 

[sxhall]

Got the file, will take a look at this evening

 

[gazmoz17]

No prob and obvs no rush

 

[sxhall]

Hi gazmoz17,

Finally gotten there with this! Have saved the file on dropbox ready to download.

Only things I could figure out was what bearing 'Apex Y/N' option has and should the blue arrow point to anything! Have rearranged the data a bit on the directory and range tabs. Would suggest hiding htese and protecting them to stop users changing anything on them.

Other than that attached has a sheet called 'Calc launch Button' on which there is a button to launch the userform.

Hope it all goes well, there is a lot in this little userform

sxhall

 

[gazmoz17]

Wow looks superb mate I’ll have a look this evening. Sorry that was my fault I thought id sent a seperate message regarding the apex (to ignore it as a calc for now). Its just ref really to cap the overall output, but a variable for capping it I wouldnt be able to feed into here. And its not a big manual calc really the capping decision. That looks so cool and will be so helpful for us at work. My colleagues bless… manually typed the postcodes up and do a control f search for postcode colour and then all manual from there. Been on my list for ages to do this for them but I have no experience of user forms to make it look more professional etc