Hiding information on sheet behind a form

[Duncan]

Hello,

Does anyone know of a quick way to hide the sheet that the form is
submitting info to? I have experimented with worksheets.visible = true
but then i would need at least one sheet that is visible. my book
contains only one sheet and my form is referring to cells via
activecell.value = (fieldname) to submit the data upon clicking the
button but if i made another blank sheet so i could hide the proper one
i would have to change my input coding. (or would I?)

i also tried with making the form massive but you can still drag it out
of the way and also i would have to cater for differant screen
resolutions.

I'm not really good with vba macros (I'm really learning as i go along)
and it has took a good two days solid of experimenting to arrive where
i am now (which should take a competent programmer about an hour!) so
does anyone know of a real simple solution that im too simple to know
about?!

[Tom Ogilvy]

If you just added a sheet, then made the data sheet hidden, you would need
not additional code to manage the added blank sheet.

--
Regards,
Tom Ogilvy


"Duncan" <[email protected]> wrote in message
news:[email protected]...
> Hello,
>
> Does anyone know of a quick way to hide the sheet that the form is
> submitting info to? I have experimented with worksheets.visible = true
> but then i would need at least one sheet that is visible. my book
> contains only one sheet and my form is referring to cells via
> activecell.value = (fieldname) to submit the data upon clicking the
> button but if i made another blank sheet so i could hide the proper one
> i would have to change my input coding. (or would I?)
>
> i also tried with making the form massive but you can still drag it out
> of the way and also i would have to cater for differant screen
> resolutions.
>
> I'm not really good with vba macros (I'm really learning as i go along)
> and it has took a good two days solid of experimenting to arrive where
> i am now (which should take a competent programmer about an hour!) so
> does anyone know of a real simple solution that im too simple to know
> about?!
>

[Duncan]

Tom,

I tried to do this earlier, (maybe i done something wrong), the problem
is when i open up, the blank sheet i have added is the active sheet, so
when i run the form it is looking to the blank sheet which is in view
and not the sheet with the data in to add stuff to. thats the bit im
trying to avoid as i would have to figure out how to get it to refer to
the hidden sheet as there would be more than one.

for the time being i have added some lines in which hide the rows when
it starts up, unhides them to add the data, then hides them again. this
might slow it down though when i have filled around 25,000 lines (which
is what i expect to fill, hopefully)

[Duncan]

Tom,

I tried to do this earlier, (maybe i done something wrong), the problem
is when i open up, the blank sheet i have added is the active sheet, so
when i run the form it is looking to the blank sheet which is in view
and not the sheet with the data in to add stuff to. thats the bit im
trying to avoid as i would have to figure out how to get it to refer to
the hidden sheet as there would be more than one.

for the time being i have added some lines in which hide the rows when
it starts up, unhides them to add the data, then hides them again. this
might slow it down though when i have filled around 25,000 lines (which
is what i expect to fill, hopefully)

Duncan

[Tom Ogilvy]

If you code uses activesheet, then you are correct that that would have to
be changed.

Dim sh as Worksheet
set sh = worksheets("MyDatasheet")
then use sh instead of activesheet. Also, you can't use select and
activate on a hidden sheet, but I would assume your code has gotten beyond
the need to do that.

--
Regards,
Tom Ogilvy


"Duncan" <[email protected]> wrote in message
news:[email protected]...
> Tom,
>
> I tried to do this earlier, (maybe i done something wrong), the problem
> is when i open up, the blank sheet i have added is the active sheet, so
> when i run the form it is looking to the blank sheet which is in view
> and not the sheet with the data in to add stuff to. thats the bit im
> trying to avoid as i would have to figure out how to get it to refer to
> the hidden sheet as there would be more than one.
>
> for the time being i have added some lines in which hide the rows when
> it starts up, unhides them to add the data, then hides them again. this
> might slow it down though when i have filled around 25,000 lines (which
> is what i expect to fill, hopefully)
>
> Duncan
>

[Mike Fogleman]

Tom, even if he Activated the hidden sheet, Activecell would still refer to
the visible sheet. He would need to change some code to qualify the range on
the hidden sheet.

Mike F
"Tom Ogilvy" <[email protected]> wrote in message
news:ue3r97$%[email protected]...
> If you just added a sheet, then made the data sheet hidden, you would need
> not additional code to manage the added blank sheet.
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Duncan" <[email protected]> wrote in message
> news:[email protected]...
>> Hello,
>>
>> Does anyone know of a quick way to hide the sheet that the form is
>> submitting info to? I have experimented with worksheets.visible = true
>> but then i would need at least one sheet that is visible. my book
>> contains only one sheet and my form is referring to cells via
>> activecell.value = (fieldname) to submit the data upon clicking the
>> button but if i made another blank sheet so i could hide the proper one
>> i would have to change my input coding. (or would I?)
>>
>> i also tried with making the form massive but you can still drag it out
>> of the way and also i would have to cater for differant screen
>> resolutions.
>>
>> I'm not really good with vba macros (I'm really learning as i go along)
>> and it has took a good two days solid of experimenting to arrive where
>> i am now (which should take a competent programmer about an hour!) so
>> does anyone know of a real simple solution that im too simple to know
>> about?!
>>
>
>

[Tom Ogilvy]

I would shoot myself if I used activecell to write information from a
userform to a worksheet, for an application that is expected to write 25,000
lines. <g> But maybe I assume too much.

--
Regards,
Tom Ogilvy




"Mike Fogleman" <[email protected]> wrote in message
news:[email protected]...
> Tom, even if he Activated the hidden sheet, Activecell would still refer
to
> the visible sheet. He would need to change some code to qualify the range
on
> the hidden sheet.
>
> Mike F
> "Tom Ogilvy" <[email protected]> wrote in message
> news:ue3r97$%[email protected]...
> > If you just added a sheet, then made the data sheet hidden, you would
need
> > not additional code to manage the added blank sheet.
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> > "Duncan" <[email protected]> wrote in message
> > news:[email protected]...
> >> Hello,
> >>
> >> Does anyone know of a quick way to hide the sheet that the form is
> >> submitting info to? I have experimented with worksheets.visible = true
> >> but then i would need at least one sheet that is visible. my book
> >> contains only one sheet and my form is referring to cells via
> >> activecell.value = (fieldname) to submit the data upon clicking the
> >> button but if i made another blank sheet so i could hide the proper one
> >> i would have to change my input coding. (or would I?)
> >>
> >> i also tried with making the form massive but you can still drag it out
> >> of the way and also i would have to cater for differant screen
> >> resolutions.
> >>
> >> I'm not really good with vba macros (I'm really learning as i go along)
> >> and it has took a good two days solid of experimenting to arrive where
> >> i am now (which should take a competent programmer about an hour!) so
> >> does anyone know of a real simple solution that im too simple to know
> >> about?!
> >>
> >
> >
>
>

[Mike Fogleman]

The op admits he is learning VBA and is struggling. Perhaps if he posted the
code he has, we could help point the way around some common pitfalls with
VBA that a beginner could come up against.

Mike F
"Tom Ogilvy" <[email protected]> wrote in message
news:%[email protected]...
>I would shoot myself if I used activecell to write information from a
> userform to a worksheet, for an application that is expected to write
> 25,000
> lines. <g> But maybe I assume too much.
>
> --
> Regards,
> Tom Ogilvy
>
>
>
>
> "Mike Fogleman" <[email protected]> wrote in message
> news:[email protected]...
>> Tom, even if he Activated the hidden sheet, Activecell would still refer
> to
>> the visible sheet. He would need to change some code to qualify the range
> on
>> the hidden sheet.
>>
>> Mike F
>> "Tom Ogilvy" <[email protected]> wrote in message
>> news:ue3r97$%[email protected]...
>> > If you just added a sheet, then made the data sheet hidden, you would
> need
>> > not additional code to manage the added blank sheet.
>> >
>> > --
>> > Regards,
>> > Tom Ogilvy
>> >
>> >
>> > "Duncan" <[email protected]> wrote in message
>> > news:[email protected]...
>> >> Hello,
>> >>
>> >> Does anyone know of a quick way to hide the sheet that the form is
>> >> submitting info to? I have experimented with worksheets.visible = true
>> >> but then i would need at least one sheet that is visible. my book
>> >> contains only one sheet and my form is referring to cells via
>> >> activecell.value = (fieldname) to submit the data upon clicking the
>> >> button but if i made another blank sheet so i could hide the proper
>> >> one
>> >> i would have to change my input coding. (or would I?)
>> >>
>> >> i also tried with making the form massive but you can still drag it
>> >> out
>> >> of the way and also i would have to cater for differant screen
>> >> resolutions.
>> >>
>> >> I'm not really good with vba macros (I'm really learning as i go
>> >> along)
>> >> and it has took a good two days solid of experimenting to arrive where
>> >> i am now (which should take a competent programmer about an hour!) so
>> >> does anyone know of a real simple solution that im too simple to know
>> >> about?!
>> >>
>> >
>> >
>>
>>
>
>

[Duncan]

Mike Fogleman wrote:

> The op admits he is learning VBA and is struggling. Perhaps if he posted the
> code he has, we could help point the way around some common pitfalls with
> VBA that a beginner could come up against.
>

Mike and Tom, Many thanks for your submissions to this post, I must
admit I am an extreme learner and have not used vb ever before about 3
weeks ago now. I have got the code working and instead of hiding the
sheet i put some code in the beginning of each sub which hid 65000
rows, this has solved my problem initially and I am starting to go
deeper into vb (with MUCH help from the friendly people on posting on
google groups). However, I understand this is perhaps killing a fish
with a grenade and there must be a correct way to achieve my result and
admittedly i have used a lot of "activecell" and "activesheet" and
"active-most-things!" as this was the first way that i found to
manipulate the data input and i guess i never thought of the drawbacks
that i could be creating as it worked for me.

If you are still willing to help me I will post my code and perhaps i
could get a crash course in how not to take the beginners path?

If you dont have the time that is ok as i have got it working to a
fashion and it only needs a few tweaks to get it how i want (i need to
add some validation now, things like duplicate entries and dates within
a certain period etc etc)

Apologies for not coming back to this post for a while as i went a bit
zombie as i got deeper into this project!

Duncan

[Duncan]

(im actually sat down now and desperately trying to work out how else i
can submit the data without doing the "activecell offset" and then
activecell.value = textboxname!

[Mike Fogleman]

Duncan, I would be willing to work with you in my off time, one on one, via
email. Since your workbook contains a form, you can't submit that to a
newsgroup. so just email it to me with a description of what you intend to
do.
You should be able to "reply to sender" and attach the workbook.
Mike F
"Duncan" <[email protected]> wrote in message
news:[email protected]...
> (im actually sat down now and desperately trying to work out how else i
> can submit the data without doing the "activecell offset" and then
> activecell.value = textboxname!
>

[Duncan]

Mike,

Although I am touched by your generousity, If i am completely honest I
simply cannot email you work and expect you to do it for me. I am going
to buy myself a Visual Basic book and see if I can't sit down and learn
some more of this as i am truly excited by vb now that i have started
to scratch the surface!

I cannot thank you enough for your offer of help.

Duncan