Thanks - I was just looking at that - hopefully this will be all. If we end up adding more worksheets - thus more test plan results on the cover sheet, what do I need to modify in the formula in column K?
Posts by mike_calc
-
-
Thanks. One last question if you don't mind. Attached is the latest copy of the workbook - some of the row numbers changed on the coversheet tab due to formatting and feedback. I updated the code best I can using what you sent. The only thing that I can't get to work is the calculations in column K. I copied your formula over and hit CTRL-Shift-enter to make in an array and did a steer and compare - I can't find the place to update.
-
Thanks Carim,
This looks really good. One issue that I found is on the cover tab - when I select YES for Session Manager in cell B13 the corresponding worksheet does not appear but when I select YES for Survivability in Cell B12 I get both Survivability and Session Manager worksheets to appear. The calculations in row K are working
-
Thanks, Carin,
I'll admit my skillset in names is probably not as good as it should be. Would names allow the calculation to be dynamic in the sense that hiding and un-hiding a worksheet is selectable by the user.
-
Hello,
I’m trying to figure out how I can do a SUM formula across worksheets and ONLY count the cells on the worksheets that are visible and ignore the cells when the worksheet is hidden.
In the attached – the “cover sheet” tab cells B10:B15 are designed to allow users the ability to hide or unhide cells. Straightforward Yes/No selection.
The formulas in K12:K17 on the same tab count the corresponding cells on all the remaining worksheets F1:F7 (except F2). I’d like this formula to ignore the corresponding cells when the worksheet is hidden.
Example – I select NO in cell B12 on the Cover Sheet Sheet Tab. This hides the Call Center tab from view. I don’t want any of the data in the call center tab cells F1:F7 to be counted on the Cover Sheet tab.
I can’t use VBA code as we have users who are using MAC devices –
Thanks
-
Thanks Mumps,
I added the code to the CommandButton1 code and I now get a different error when attempting to add any virtual hosts the first time. Run-time error ‘91’: “Object Variable or With block variable not set. It points me to this line in the code
y = fnd.Row + 11
Also, just so I can understand, you changed the 20 to 13 – what does that number represent?
-
In the attached workbook, I needed to make a change to one of the “names” – instead of using the name “Avaya Virtual Host ” – I need to drop the word Avaya and go with just “Virtual Host ”. When I made the change to the command button 1 code (labeled Virtual Hosts), the macro runs as expected. I’m prompted to enter a QTY of hosts and the macro copies rows 27:37 and pastes them immediately below and updates the numerical identifier for that particular host.
The error happens when I go back and try to add any QTY of additional hosts - I get a run-time error 13 “type mismatch”. I’ve looked at the debugger and it points to this line in the code “ Range("A" & y) = "Virtual Host " & Mid(fnd, 20, 999) + z” - I can’t figure out why this is not working when the only thing I’ve changed is the name from “Avaya Virtual Host ” to “Virtual Host ” throughout the code.
-
Hi Roy, Will do - this is somewhat a complex WB so I figured I'd start with someone who helped with the original code. Will start a new thread - thanks
-
Hello Mumps,
I hope you are well and don’t mind reaching out - I figured I reengage you as you’re most familiar with this workbook.
In the attached workbook, I need to make a change to one of the “names” – instead of using the name “Avaya Virtual Host #” – I need to drop the word Avaya and go with just “Virtual Host #”. When I make the change to the command button 1 code, the macro runs just fine when I enter any QTY of hosts the first time. It copies rows 27:37 and pastes them below and updates the numerical identifier for that particular host. When I try to go back and add additional hosts I get a run-time error 13 “type mismatch”. I looked at the debugger and can’t figure out why this is not working when the only thing I’ve changed is the name from “Avaya Virtual Host” to “Virtual Host” throughout the code.
Any help is greatly appreciated.
Thanks
-
Thanks - I looked at everything for over an hour and never thought to see if those cells were merged. Thanks Dave -
-
Hi Dave,
I’m hoping you could take a quick look at the attached and figure out why one copy/paste macros aren’t working like all the others. Cells E:I contain the cells that I want copied when a user selects the picture for that phone type – the cells are pasted starting in column K and continue out depending on how many templates the user requests. In the attached, I selected 2 for each of the phone types. In rows 69:83 the macro is pasting the template starting in column J and not column K like all the others. I’ve been through the code with my limited knowledge, and the macro appears to be the same (except for the row numbers), but the output is not the same.
-
Thanks and it's always the simplest things -
One more question – when I enter a QTY for templates needed I get 1 more than requested. This is simply due to the template being used – my thought is to hide the template columns E:I so the user will always have a clean template to copy from. Then when they enter 2 – they only see 2. When I hide these columns and then run the MACRO I get a run time error 1004.
-
The revised code works perfectly – thanks.
However, I think I broke it – I cleaned up the formatting and removed some rows – I went into the code line “Set rng = .Range("E20:I34")” and updated the cell range - now it only copies 1 template (regardless of the QTY I enter) and pastes it into row 10 column AG. I think the error may lie in the line “rng.Copy .Range("XFD10").End(xlToLeft).Offset(0, 5)” – I don’t understand the XFD10.
I need the same behavior for all the other phone types – which I can copy/paste from what you provided and update the appropriate lines.
Thanks
-
Thank you again - I'll run through it in a bit and let you know. Much appreciated
-
Yea, I thought that after about 3 hours of beating my head against the wall - thanks for helping. The one thing I noticed is when I click on the picture (which like) and add a few templates - and then go back a add a few more - the original ones added disappear. I need them to stay -
-
Thank you, Dave. I inserted both codes and it sort of worked. Here is what I’m experiencing –
See the attached –
When I save/close the file and then reopen – all the commandbuttons are stacked down around row 147 instead of above the various pictures where they were – and I only see the commandbuttons when I have developer mode turned on. If Developer Mode is off I don’t see any command buttons.
When I move the command buttons back to the right locations and click on the 9608 Command Button – I get the pop-up message to add a value – for testing I entered 3 and there were 3 templates copied over. I then reselected the 9608 command button and entered 2 – as if I was adding 2 more templates after the fact – in this scenario I don’t get any additional templates added.
Thanks
-
I have a worksheet where I’m attempting to do a couple of things –
- Use checkboxes to allow the user to choose varying styles of phones to configure. When they check a box, this will unhide rows within the worksheet specific to the selection they made. This functionality is in row 5 of the attached and is working.
- When the checkbox is unchecked I need it to hide the rows (this works) along with the command button within that row (this is not working). If I group the command button with the picture, then it will hide/unhide with the row, but the functionality of the command button stops working. Is there a way to hide the command button when the corresponding checkbox is unchecked?
- When a user selects the command button, they are prompted to enter a numerical QTY and based on that QTY, copy cells E10:I24 and paste it as many times as the QTY they entered - starting in cell K10. On the attached, you can see how it should look if the user entered 4. This part I can’t figure out – the code I used was from another worksheet that had similar functionality - but row based. This needs to be column based and row dependent on the various items..
Thanks for any help/guidance
-
-
Hi Mumps,
I just noticed that when we select YES in cell G4 or cell H4 – the associated tab is created and renamed appropriately. However, the template for that application is made visible. By default the template tab will be veryhidden.
Example, I select yes for Session Border Controller and a new tab is created using the session border controller template. I see the new tab with SBC appended to the end of the tab name, and I see the Session Border controller tab. See attached
-
The few tests I ran - perfect. Thanks Mumps.