Posts by mike_calc

    Thanks, I copied the revised code and ran through the 2 scenarios and received the same results.


    Scenario 1

    Select “Avaya Virtual” from cell D4


    Click Avaya Hosts in column E and enter 3


    Select Media Gateways and enter 3 – at this point I don’t get the media gateway header row to be visible.



    Scenario 2

    Select “Avaya Virtual” from cell D4


    Click Avaya Hosts in column E and enter 3


    Click Avaya Hosts again in column E and enter 4


    Select Media Gateways and enter 3 – at this point I get a run-time error ‘91’ – Object Variable or With block variable not set – when I click on debug it points to “Rows(MG.Row + 1).EntireRow.Hidden = False”

    Might have spoke too soon :) -


    Try these scenarios –



    Select “Avaya Virtual” from cell D4


    Click Avaya Hosts in column E and enter 3


    Select Media Gateways and enter 3 – at this point I don’t get the media gateway header row to be visible.





    Select “Avaya Virtual” from cell D4


    Click Avaya Hosts in column E and enter 3


    Click Avaya Hosts again in column E and enter 4


    Select Media Gateways and enter 3 – at this point I get a run-time error ‘91’ – Object Variable or With block variable not set – when I click on debug it points to “Rows(MG.Row + 1).EntireRow.Hidden = False”

    No. If column B is blank there would be no value set in column C. I'd like to keep the row directly under Media Gateway header that we are using to copy the other rows blank and hidden - this way a user can't populate the cells and copy it over and have to go back and manually delete or change values. This way they get a clean slate to work off of..

    Much better - what I see now is when I add gateways (say a QTY of 5) the macro is only adding 4. Tried a few different QTY's and it is coming up 1 short each time. Also, when I add additional gateways - these are being populated before the rows where the previously added gateways are. Anytime I add gateways they should be added below the last gateway

    I’m perplexed, crazy, or both – it seemed to be working when I ran through a few test scenarios but now it’s not – attached is the output from when I added hosts and gateways on the Primary Core tab



    I select one host, and rows 27:33 are visible, and row 27 is labeled and numbered correctly. Then I try to add five gateways, and instead of the new rows being added below the media gateway header row, they were inserted in the middle of the Host 1 rows (rows 30:34).



    I delete the Media Gateway rows from rows 30:34. I select Hosts and add a QTY of 3 hosts – this populates rows 27:47 correctly with the right numerical label. I then reselect Hosts and add 2 more – for a total QTY of 5. This added rows 48:61 and labeled them correctly. I now select Gateways and enter a QTY and I receive a Run-time error ‘91’ – Object variable or With block variable not set. When I click on debug it highlights this line in the INR_PrimaryCoreGateway code “ Rows(MG.Row).EntireRow.Hidden = False”. I tried changing this to True and the same error.



    I then exit out of Excel and relaunch the file – the Run-time error 91 goes away but the media gateways are being added back up in row 30

    One more question –



    On the INR_PrimaryCoreGateway macro – the formatting is only being applied to columns A – L and not for M – P. I see in the MACRO where the columns are selected and it is only selecting A – L. I can’t figure out how I extend the selection of columns to P in the code.



    Sub INR_PrimaryCoreGateways()


    Dim MG As Range


    Set MG = Range("A:A").Find("Media Gateways")


    Rows(MG.Row).EntireRow.Hidden = False


    Range("A" & MG.Row + 1).Resize(, 12).Copy


    Range("A" & MG.Row + 2).Select


    ActiveCell.EntireRow.Insert Shift:=xlDown


    Application.CutCopyMode = False


    Application.ScreenUpdating = False


    End Sub

    Thank you again -


    All my testing is being done on the Primary Core tab


    When I select the HOST button in E3 and then select cancel, I get a Run-time error ‘13’ – Type Mismatch.



    When I select 2 hosts – I get a second host added and labeled correctly. However, the contents of the cells are being copied over. When a new host is added, only the formatting and drop-down lists should be copied.



    When I add 3 more hosts (for a total of 5) I only end up with 4 and the new hosts are being added before the existing ones and the number of the host is starting back at 1 – see attached for how the results are showing. Any new hosts should follow behind any existing



    When I add Media Gateways the new rows are being inserted before the existing rows – is there a way to have the new rows be added after the last media gateway? On the attached I added 3 media gateways – labeled these existing in column C and then added 2 more (labeled these added) – as you can see the new that were added at a later date we inserted in front of the existing.

    Ok, and that makes sense. I was hoping to find the area in the code to modify so as changes pop up I can easily address. Attached is what I anticipate being the last revision based on my internal review with the end users.



    On the “Primary Core” I have rows 6:26 that are specific to Customer Provided VMware Environment and rows 6:10 and 21:26 are visible when D4 is CPE Virtual. Rows 11:20 are hidden but the user can unhide them if they need more rows that what is visible.



    Rows 27:33 is the Avaya Virtual Host 1 and when cell D4 is Avaya Virtual, these rows are visible. This is where you modified the code earlier and I need to align the row numbers with.



    Rows 6:33 are all visible when cell D4 is set to both.



    Column A allows users to enter an N and hit enter to hide that particular row.

    Thank Mumps,



    I’ll say that this latest one has me stumped – LOL



    I can’t figure out where you set the row values for the Avaya Virtual Host copy/paste – I thought under the CommandButton 1 sub I see in a few spots rows 13:19 are identified and those match up with the worksheet. I ended up adding a few rows under the CPE Virtual heading – which are rows above the Avaya Virtual Host. When I change the values from 13:19 to what are now 27:33 I seem to screw up the macro. Where do I modify the rows being copied/pasted?

    Hi Mumps,


    Thank you again for your assistance on this workbook. I’ve asked some internal users to run through a couple test scenarios to make sure everything is good. Couple things I’d like to run by you –


    • On the attached, I created a worksheet called SJMC. On this sheet, I added two hosts using the command button labeled “hosts” – this generated two hosts labeled “Avaya Virtual Host 1” and “Avaya Virtual Host 2” – which is perfect. Now, when I try to add one more host (for a total of 3) - I select the command button and enter 1 – nothing happens. This should create a 3rd host and label it according to the next available number – in this case “Avaya Virtual Host 3” – this is not happening. It’s actually creating the host and naming it “Avaya Virtual Host …. reusing numbers” and then inserting the new hosts in between the existing ones. Is there a way to keep the host numbers labeled sequentially and have them populate in order?
    • Is there a way to clear the contents of the cell when creating a host? Let’s say I’ve come back in – weeks later - and need to add another host. When the new host is created the cell contents of Host 1 is copied forcing the user to delete the contents from the new host. Is there a way to have the host created and its cell contents not be copied over?
    • Also, when I select yes for Session Border Controller or AADS in cells G3 or H3 the new tab is created – works as expected. Is there a way to have the new worksheet created next to the active tab where I selected the value. Currently the newly created tab is inserted at the end of the worksheets.

    Hi Mumps,



    Quick question – is there a way to set the template tabs to be “very Hidden” and leave them like that? When I go in and set the properties for the individual tab to XlSheetVeryHidden the tab is very hidden and the user can’t unhide it, but when the user enters a new site on the site list the veryhidden tab is now only hidden. Is there a way to keep the template tabs veryhidden?



    My fear is that someone will accidently modify the template and mess everything up – if I can make it so they can’t unhide it then I think I’d be safe.

    Hopefully, last questions.


    Is there a way to keep the active tab visible when a new tab is created? In the file I sent you – when I change the value on the admin tab in either cell G3 or H3 to Yes, the new tab is created and it takes me to that new tab. I’d like for the user to remain on the main tab (admin).



    Also, the number of tabs could grow to over hundred in this file, is it possible (like we did on the site list tab) to create a hyperlink to the newly created tab and insert that hyperlink in either G4 or H4? This would make it much easier to navigate