Bare Knuckle Pickups Forum
At The Back => Time Out => Topic started by: _tom_ on October 09, 2006, 10:40:10 PM
-
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
-
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
-
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
-
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
-
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..
-
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)
-
Ok, cheers for the help. Hopefully can get this working so I can move on.. such a boring task
-
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
-
activeworkbook.saveas filename:=sFileName
my mistake, this should be activesheet not activeworkbook for saving that sheet
-
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:
(http://img222.imageshack.us/img222/9917/untitledmw6.jpg)
-
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 :wink:
-
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
-
Oh.. shows what I know then :lol: Cheers for all your help, hopefully can get this sorted out soon.
-
why do you have two "end sub" statements??
-
and what is the name of your button???
-
OK, works for me when i use this code (see screenshot)
how did you get the cmdbutton14_click() statement? did you type it in??
-
Yeah I typed in cmdButton14_click() in.. I'll follow your code so hopefully it will work..
-
what's the exact name of your button?
if it's definitely cmdButton14 then something else is wrong, but i'd put money on it just being Button14
-
Yeah its called Button14. I dont know why I called it cmd :lol: Absolutely useless. Cheers for the help
-
that'll be the problem then, what do i win :lol:
-
The satisfaction of helping someone who's shitee at programming? :lol:
I dont have any real prizes :D
-
lol, thas kool, i'll just go back to being smug
-
Yay it worked :D Now.. how the hell do you change the colour of macro buttons in excel? In format controls theres no bit to change the colour of the button, and that grey looks awful :(
-
you can't i don't think
the best thing you can do in that situation is to put a picture of a button in there, and then put another graphic in for the rollover.