I am confused about a macro I have built.

Marq

Well-known Member
Joined
Dec 13, 2004
Messages
913
Office Version
  1. 365
  2. 2007
Platform
  1. Windows
using 365

I have a work book that I use to track progress at work. I have a macro built in it where I populate about 10 cells on one sheet, then click a macro button and the data I populated in the 10 cells automatically populates my headers (left, center and right).

So I will open up the workbook which is titled "Blank Progress"...I will load the data in the cells, click the button anticipating the headers populate and then id save it as the new jobs name. (I automated my headers because I have to put the same headers on multiple sheets and got tired of manually doing it)

Last week I edited the macro in VB to include some new header info I need to start seeing and ever since then when I click the button, another workbook that's already populated for a job opens up and only then will the automated header button work in the Blank Progress.

ALSO....when I open the blank progress work book and go to view the macro, it isn't showing up..i got to View/Macros/ and its chosen on "This Work Book" and its blank...there are no macros to view.

The only time I can see the macro in the View/Macros pop up window is if that other work book that somehow opens up whenever I click the button on the Blank progress work book.

I hope I did not confuse anyone with my description but I could use some help figuring out how to fix this.
 

Marq

Well-known Member
Joined
Dec 13, 2004
Messages
913
Office Version
  1. 365
  2. 2007
Platform
  1. Windows
I have figured out what the issue is (I think)...its a matter of space. I am limited to the amount of characters I can put in a line for a header.

So if you look at the attached pic, you will see my permanent line items, that are on every left header.

The cells I populate then run the macro then feed to each individual line...so for example I write out only a few lines of the left header as to what it will look like once macro is ran.

Before marcro is ran its:

OPS-PO
Enclose/PC:
RemoveINS:
InstallINS:

AFTER the macro is ran its:

OPS-PO
Enclose/PC: 0010-00100
RemoveINS: 0020-00200
InstallINS: 0030-00300

Well, I have 7 lines in that left header (as shown in the pic) and being I already have some permanent text in the header, it means im limited to what I can add AFTER the colons. I can remove the space from between the : and the fist digit, but that still wont free me up enough real-estate to fill in the rest of the XXXX-XXXXX for all seven lines.

So now the issue turns into "how can I get all lines to give me enough space to have the XXXX-XXXXX next to it.".

Would a smaller font work??? I have abbreviated as much as I can for the permanent text so shortening the words wont help.

so the code is good..i just don't have enough left header space. Putting it on the right header or center is out of the question....I MAY be able to move one to the center and still achieve what I want. let me try that...I will report back.
 

Attachments

  • left header 1 27 21.PNG
    left header 1 27 21.PNG
    2.8 KB · Views: 0

Some videos you may like

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

Marq

Well-known Member
Joined
Dec 13, 2004
Messages
913
Office Version
  1. 365
  2. 2007
Platform
  1. Windows
ok..here ya go...I was able to move the ASBESTOS? permanent line to the center header, which then freed up left header character space.

I got the left header to accept all of the XXXX-XXXXX's

But NOW the center header is limited to character space.

So apparently if you run a VB code for an excel header you are limited to the amount of characters you can use overall in all 3 sections. Freeing up space in one part of the header removes space from another header section.

Using a footer is not an option..everything needs to be at the top of the sheet.

Figuring out how to pull this off is a tough one.
 

Marq

Well-known Member
Joined
Dec 13, 2004
Messages
913
Office Version
  1. 365
  2. 2007
Platform
  1. Windows
I figured out how to shorten things to give me room. I made Insulation progress into INS Progress.

I also remove the day of the week code in the right header.

Doing those two things freed up the exact amount of characters I needed for all 3 header sections.
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,128,129
Messages
5,628,869
Members
416,347
Latest member
AT2021

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top