How do I make a forms button invisible please?

[aaron.irvine]

Hey guys,

This one has had me on the ropes for a few days, I'm going to have to ask for some help here if anyone can assist.

I've created a series of buttons on a spreadsheet that will essentially unhide specific rows for data entry purposes. I would like to be able to delete or make these buttons invisible once they have been clicked for housekeeping purposes.

The code I have written to create the button exists in the macro that imports an external sheet for people to enter their data.

Please Login or Register  to view this content.

Within the macro code I've only got a small amount of code to unhide the rows. Somewhere here I would like to delete or make the button invisible, but clearly I can't figure it out.

Please Login or Register  to view this content.

Anyone who can help me out here will be saving my week!

[humdingaling]

syntax should be

Please Login or Register  to view this content.

or if your case i think your just missing . ? because you have Set already?

[aaron.irvine]

I didn't even realise I was missing the . in there.

Does it matter if I created the button in one sub, and yet try to make it invisible when triggering another sub?

At the end of the main sub I have coded:

Please Login or Register  to view this content.

If I remove the last line, NewShift7 will carry it's value into the NewShift7 Sub? Is there a chance of conflict with the naming conventions I'm using here?

Thank you.

[aaron.irvine]

Well that didn't work. I think I need to make the NewShift7 variable global? Not sure where I would declare it though....

[humdingaling]

oh yes, sorry you had two seperate codes

instead of DIM use PUBLIC

otherwise use the full syntax instead of the declared variable syntax
as in post #2

[aaron.irvine]

Unfortunately as I'm declaring these variables within the newSheet() module for importing a sheet into the workbook, it's not allowing me to declare a public variable at this point in the code - and I'm feeling so n00b right now I can't remember where I need to be declaring the public vars!

[humdingaling]

is the button actually called newshift7? i think your better off using the full syntax in this case instead of using public variable (unless your using it for something else in your overall code)

public variables need to be outside the sub

any chance of getting a sample up so i can see the whole thing?
as i am only guessing what setup you have and trying to replicate it on my sample workbook....the outcomes are probably different

[aaron.irvine]

Hey mate,

I just gutted the spreadsheet - as it's a work document (felt good actually haha)

Hopefully it's got enough in it to see what I'm trying to do. I know the code structure is terrible, but it's working for me!

I would simply like the buttons on the left-hand side to disappear once "...Next Test" or "Shift Handover" is clicked - so that there is only ever ONE set of buttons in the lower-left of the spreadsheet.

Hope it makes sense.

Cheers

[humdingaling]

vba is password protected

anyway first thing i notice is the shift handover button is called button 8

so maybe give this a go

Please Login or Register  to view this content.

[aaron.irvine]

Sorry mate, that was pretty amateur of me. Gotta protect your code around my workplace

Here we go.

How do you determine the name of the button? IE - "Button 8". Bit of a stupid question, but I can't find that reference anywhere.

Did you back a winner yetsterday?

[humdingaling]

unfortunately not, bloody red whatsitsname ruined the trifecta
ar wells
hope you fair better than i

the name is in the name box
http://www.msexcel07.com/naming-cells.htm

ok so i got it to work on your sheet

Please Login or Register  to view this content.

and under newShift1
ActiveSheet.Shapes("Button 8").Visible = False

i assume you have some mechanism to make it reappear if you don't, just visible = true on something

you sure do have alot of macros worked up
you could probably consolidate all your newTest/shift macros into one but maybe as a separate training exercise

[humdingaling]

hmm after looking at your postmidnight macro...i see how you add the button in
meaning your button name changes everytime you add something

in this case...you should probably put into the macro the changing the actual button name (maybe as well as setting variable) at the time you add

Ie add this line under the caption equivalent
newShift1.Caption = newShiftText
newShift1.Name = newShiftText

[aaron.irvine]

Thanks heaps for your help mate, that should give me the result I'm looking for.

I knew it'd be a whole lot more coding than I needed, but there's plenty of copy n paste going on there, and I've had enough thinking to do as it is!