Need some help from those good at Excel/Visual Basic

[_tom_]

I'm trying to create a button which when clicked will save the active worksheet as the number in a cell in the same worksheet. I found this but it doesnt seem to work. I get an error message in Visual Basic editor http://www.mrexcel.com/tip040.shtml

Mine dont have the Public Sub bits, just code for the button. I'll have a look at school tomorrow to show you exactly what all my buttons codes are but its definately not Public sub, something like "Click_Button_1()"

Any help would be great, coz this is really pissing me off

[CaffeineJunkie]

OK, i take it you're working in VBA not VB (so the built in editor for Excel)

When you have a button click event, you will have something along the lines of ....

Private Sub cmdButtonName_click()

Dim sFileName as string

if len(range("A4").value) <> 0 then
    sfilename = range("A4").value
    activeworkbook.saveas filename:=sFileName
else
    msgbox "Nothing in cell"
end if

end sub


what error were you getting when you ran it????




James

[_tom_]

I'll have a look tomorrow, will try that code you suggested. I'll printscreen the error if it still comes up. Really need to get excel working on this computer again  :x

[CaffeineJunkie]

kk, although another alternative is to define a function which does the saving, and then you only need to build the code once, and then refer to it for each button you put in, making it easier to change if you have lots of buttons... but it depends on what you are trying to build

[_tom_]

Well I dont know what the hell that means :lol:

Basically I've just got to create an order form/invoice for a company, and I only need 1 button to save each invoice as the invoice number I have on the sheet..

[CaffeineJunkie]

oh right, in that case use the code from above, although be sure to save it in the right place, else it will automatically save straight to the default directory

(i would use app.path, but i don't think that works in VBA, so i shall look it up in a sec)

[_tom_]

Ok, cheers for the help. Hopefully can get this working so I can move on.. such a boring task

[CaffeineJunkie]

naah, i love programming me, usually spend most of my time in computing doing the work rather than the design etc.

for my current project i've already learnt one language, and now i have to learn another one, :D:D dunno why, but i love it all :D:D:D

[CaffeineJunkie]

activeworkbook.saveas filename:=sFileName

my mistake, this should be activesheet not activeworkbook for saving that sheet

[_tom_]

naah, i love programming me, usually spend most of my time in computing doing the work rather than the design etc.

for my current project i've already learnt one language, and now i have to learn another one, :D:D dunno why, but i love it all :D:D:D

haha I'm the other way round. Good at designing stuff, rubbish at programming what I want the stuff to do :lol:

Anyway, I tried your code, this is what it came up with:

[ToneMonkey]

Good luck mate, I did a fair bit of VB at uni (aswell as C, C++ and some other stuff that I can't remember) and I don't miss it at all.  Infact the vast majority has been filed in the "Don't think I'll be using this again" section of my brian.  It was a but odd to look at it again.

Good luck

[CaffeineJunkie]

thas nothing to do with the code, it's more to do with when you are executing the code, when i get home from college i'll take a closer look

[_tom_]

Oh.. shows what I know then :lol: Cheers for all your help, hopefully can get this sorted out soon.

[CaffeineJunkie]

why do you have two "end sub" statements??

[CaffeineJunkie]

and what is the name of your button???