Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hiding information on sheet behind a form
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?! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hiding information on sheet behind a form
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" wrote in message oups.com... 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?! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hiding information on sheet behind a form
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) |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hiding information on sheet behind a form
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hiding information on sheet behind a form
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" wrote in message oups.com... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hiding information on sheet behind a form
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" wrote in message ... 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" wrote in message oups.com... 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?! |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hiding information on sheet behind a form
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" wrote in message ... 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" wrote in message ... 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" wrote in message oups.com... 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?! |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hiding information on sheet behind a form
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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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" wrote in message oups.com... 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?! |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hiding information on sheet behind a form
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hiding information on sheet behind a form
(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! |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hiding information on sheet behind a form
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" wrote in message oups.com... (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! |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hiding information on sheet behind a form
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Transfering information form one cell to another work sheet | Excel Worksheet Functions | |||
Hiding information | New Users to Excel | |||
Hiding duplicate information | Excel Worksheet Functions | |||
Hiding cell information | Excel Worksheet Functions | |||
How do I put my information form Excel into a Word Merge Form? | Excel Discussion (Misc queries) |